﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Web;
using System.IO;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DB
/// </summary>
public class DB2
{
	public DB2()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    private static SqlConnection OpenConnection()
    {
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=usuarios;Integrated Security=True;Pooling=False");
            conn.Open();
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        return conn;
    }
    private static SqlConnection OpenConnection2()
    {
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=BPOphotos;Integrated Security=True;Pooling=False");
            conn.Open();
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        return conn;
    }
    public static bool ActivateUser(string UserID) {
        bool result = false;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "update tbl_usuario set usu_estado=1 where usu_id=@usuID";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = UserID.Trim().ToUpper();
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();

           /* comm.CommandText = "update tbl_usuario_perfil set upe_estado=1 where upe_usu_id=@usuID";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = UserID.Trim().ToUpper();
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();
            */
            result = true;
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static bool DesactivateUser(string UserID)
    {
        bool result = false;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "update tbl_usuario set usu_estado=0 where usu_id=@usuID";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = UserID.Trim().ToUpper();
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();
            result = true;
            

        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_User ValidaCliente(String user, String pss, String app, string perfil)
    {
        RE_User result = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        string ruta2 = "c:\\TEMP\\archivo.txt";
        StreamWriter escritor2 = new StreamWriter(ruta2, true);
       
        escritor2.Close();
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            if (perfil.Equals("-1")) {
                comm.CommandText = " SELECT usu_id, usu_nombre, usu_apellido, usu_compania, per_nombre, usu_logo, usu_alias, usu_where, usu_where2, usu_permiso, usu_restricted, sum(ape_permiso), tbl_usuario_perfil.upe_group  fROM tbl_usuario, tbl_usuario_perfil, tbl_perfil_aplicacion, tbl_perfil WHERE per_id=upe_per_id and (upper(usu_id) = @usuID) AND (usu_contrasena = @usuContrasena) and usu_estado=1 and upe_estado=1 and upe_usu_id=usu_id and upe_per_id=ape_per_id and ape_apl_id=@aplicacion and ape_per_id in (select per_id from tbl_perfil where per_catalogo=1) group by usu_id, usu_nombre, usu_apellido, usu_compania, per_nombre, usu_logo, usu_alias, usu_where, usu_where2, usu_permiso, usu_restricted, tbl_usuario_perfil.upe_group ";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = user.Trim().ToUpper();
                comm.Parameters.Add("@usuContrasena", SqlDbType.VarChar).Value = pss.Trim();
                comm.Parameters.Add("@aplicacion", SqlDbType.VarChar).Value = app.Trim();
               
            } else {
                comm.CommandText = " SELECT usu_id, usu_nombre, usu_apellido, usu_compania, per_nombre, usu_logo, usu_alias,  usu_where, usu_where2, usu_permiso, usu_restricted,sum(ape_permiso), tbl_usuario_perfil.upe_group  fROM tbl_usuario, tbl_usuario_perfil, tbl_perfil_aplicacion, tbl_perfil WHERE per_id=upe_per_id and (upper(usu_id) = @usuID) AND (usu_contrasena = @usuContrasena) and usu_estado=1 and upe_estado=1 and upe_usu_id=usu_id and upe_per_id=ape_per_id and ape_apl_id=@aplicacion and ape_per_id=@perfilID group by usu_id, usu_nombre, usu_apellido, usu_compania, per_nombre, usu_logo, usu_alias, usu_where, usu_where2, usu_permiso, usu_restricted, tbl_usuario_perfil.upe_group ";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = user.Trim().ToUpper();
                comm.Parameters.Add("@usuContrasena", SqlDbType.VarChar).Value = pss.Trim();
                comm.Parameters.Add("@aplicacion", SqlDbType.VarChar).Value = app.Trim();
                comm.Parameters.Add("@perfilID", SqlDbType.VarChar).Value = perfil.Trim();
                
            }
            escritor2 = new StreamWriter(ruta2, true);
            escritor2.WriteLine("select " + comm.CommandText);
            escritor2.Close();
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = new RE_User();
                if (!reader.IsDBNull(0)) result.userID = reader.GetString(0);
                if (!reader.IsDBNull(1)) result.nombre = reader.GetString(1);
                if (!reader.IsDBNull(2)) result.apellido = reader.GetString(2);
                if (!reader.IsDBNull(3)) result.compania = reader.GetString(3);
                if (!reader.IsDBNull(4)) result.perfil = reader.GetString(4);
                if (!reader.IsDBNull(5)) result.logo = reader.GetString(5);
                if (!reader.IsDBNull(6)) result.alias = reader.GetString(6);
                if (!reader.IsDBNull(7)) result.where = reader.GetString(7);
                if (!reader.IsDBNull(8)) result.where2 = reader.GetString(8);
                if (!reader.IsDBNull(9)) result.permisouser = reader.GetInt64(9); 
                if (!reader.IsDBNull(10)) result.restricted =long.Parse(reader.GetInt64(10).ToString());
                if (!reader.IsDBNull(11)) result.permiso = long.Parse(reader.GetValue(11).ToString());
                if (!reader.IsDBNull(12)) result.group = reader.GetString(12);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(DateTime.Now.ToString()+ e.Message.ToString()+ e.StackTrace);
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static bool InsertarCliente(RE_User usuario)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            
            
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (usuario.existe==0)
            {
                comm.CommandText = "insert into tbl_usuario (usu_id, usu_contrasena, usu_estado, usu_fecha_creacion, usu_alias, usu_nombre, usu_apellido,usu_compania, usu_email, usu_title, usu_logo) values (@usuID,@pass, 1, GETDATE(), @alias, @nombre, @apellido, @compania, @email,@title, @logo)";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                comm.Parameters.Add("@pass", SqlDbType.VarChar).Value = usuario.pss;
                if (usuario.alias == "" || usuario.alias == null)
                    usuario.alias = DB2.newUserAlias();
                comm.Parameters.Add("@alias", SqlDbType.VarChar).Value = usuario.alias.ToUpper();
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = usuario.nombre.ToUpper();
                comm.Parameters.Add("@apellido", SqlDbType.VarChar).Value = usuario.apellido.ToUpper();
                comm.Parameters.Add("@compania", SqlDbType.VarChar).Value = usuario.compania.ToUpper();
                comm.Parameters.Add("@email", SqlDbType.VarChar).Value = usuario.email;
                comm.Parameters.Add("@title", SqlDbType.VarChar).Value = usuario.title.ToUpper();
                comm.Parameters.Add("@logo", SqlDbType.VarChar).Value = usuario.logo.ToUpper();

                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_usuario_perfil (upe_usu_id, upe_per_id, upe_estado, upe_group) values (@usuID, @perID, 1, @group)";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                comm.Parameters.Add("@perID", SqlDbType.VarChar).Value = int.Parse(usuario.perfil);
                comm.Parameters.Add("@group", SqlDbType.VarChar).Value = usuario.group.ToUpper();
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                if (int.Parse(usuario.perfil) == 0)
                {
                    ArrayList pages = new ArrayList();
                    RE_GenericBean2 info;
                    comm.CommandText = " SELECT  papp_id,  (SELECT   SUM(opc_permiso) AS Expr1  FROM  tbl_option  WHERE  (opc_pagina = tbl_pageapp.papp_id)) AS Restricted  FROM  tbl_pageapp  WHERE  (papp_apl_id = @apl_id) ";
                    comm.Parameters.Add("@apl_id", SqlDbType.VarChar).Value = 1;
                    reader = comm.ExecuteReader();

                    while (reader.Read())
                    {
                        info = new RE_GenericBean2();
                        if (!reader.IsDBNull(0)) info.intC1 = reader.GetInt32(0);
                        if (!reader.IsDBNull(1)) info.int64C1 = reader.GetInt64(1);
                        pages.Add(info);
                    }
                    reader.Close();
                    reader.Dispose();
                    comm.Parameters.Clear();

                    foreach (RE_GenericBean2 page in pages)
                    {
                        comm.CommandText = " INSERT INTO usuario_permiso   (usu_id, papp_id, permiso, restricted)   VALUES   (@usu, @page, @permiso, @restricted) ";
                        comm.Parameters.Add("@usu", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                        comm.Parameters.Add("@page", SqlDbType.Int).Value = page.intC1;
                        comm.Parameters.Add("@permiso", SqlDbType.BigInt).Value = 0;
                        comm.Parameters.Add("@restricted", SqlDbType.BigInt).Value = page.int64C1;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                }
                else
                {
                    ArrayList pages = new ArrayList();
                    RE_GenericBean2 info;
                    comm.CommandText = " SELECT   ape_papp_id, ape_permiso, ape_restricted   FROM   tbl_perfil_aplicacion   WHERE   (ape_apl_id = @apl_id) AND (ape_per_id = @per_id) ";
                    comm.Parameters.Add("@apl_id", SqlDbType.Int).Value = 1;
                    comm.Parameters.Add("@per_id", SqlDbType.Int).Value = int.Parse(usuario.perfil);
                    reader = comm.ExecuteReader();

                    while (reader.Read())
                    {
                        info = new RE_GenericBean2();
                        if (!reader.IsDBNull(0)) info.intC1 = reader.GetInt32(0);
                        if (!reader.IsDBNull(1)) info.int64C1 = reader.GetInt64(1);
                        if (!reader.IsDBNull(2)) info.int64C2 = reader.GetInt64(2);
                        pages.Add(info);
                    }
                    reader.Close();
                    reader.Dispose();
                    comm.Parameters.Clear();

                    foreach (RE_GenericBean2 page in pages)
                    {
                        comm.CommandText = " INSERT INTO usuario_permiso   (usu_id, papp_id, permiso, restricted)   VALUES   (@usu, @page, @permiso, @restricted) ";
                        comm.Parameters.Add("@usu", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                        comm.Parameters.Add("@page", SqlDbType.Int).Value = page.intC1;
                        comm.Parameters.Add("@permiso", SqlDbType.BigInt).Value = page.int64C1;
                        comm.Parameters.Add("@restricted", SqlDbType.BigInt).Value = page.int64C2;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                }

                int uinID = 1;
                int tcid = 1;
                foreach (RE_Direccion direccion in usuario.direcciones)
                {
                    comm.CommandText = "select max(uin_id) from tbl_usuario_informacion";
                    reader = comm.ExecuteReader();
                    if (reader.Read())
                        if (reader.IsDBNull(0)) uinID = 1; else uinID = reader.GetInt32(0) + 1;
                    if (reader != null)
                    {
                        reader.Close();
                        reader.Dispose();
                    }
                    comm.CommandText = "insert into tbl_usuario_informacion(uin_id, uin_usu_id, uin_st_number, uin_st_add, uin_city, uin_state, uin_zip, uin_tel_oficina, uin_tel_celular, uin_fax, uin_tii_id) values (@uinID, @usuID, @stnum, @stadd, @city, @state, @zip, @telof, @telcel, @fax, @tipo)";
                    comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                    comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                    comm.Parameters.Add("@stnum", SqlDbType.VarChar).Value = direccion.stnumber.ToUpper();
                    comm.Parameters.Add("@stadd", SqlDbType.VarChar).Value = direccion.stadd.ToUpper();
                    comm.Parameters.Add("@city", SqlDbType.VarChar).Value = direccion.city.ToUpper();
                    comm.Parameters.Add("@state", SqlDbType.VarChar).Value = direccion.state.ToUpper();
                    comm.Parameters.Add("@zip", SqlDbType.VarChar).Value = direccion.zip;
                    comm.Parameters.Add("@telof", SqlDbType.VarChar).Value = direccion.telof;
                    comm.Parameters.Add("@telcel", SqlDbType.VarChar).Value = direccion.telcel;
                    comm.Parameters.Add("@fax", SqlDbType.VarChar).Value = direccion.fax;
                    comm.Parameters.Add("@tipo", SqlDbType.Int).Value = direccion.tipodir;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();

                    foreach (RE_CreditCard tarjeta in direccion.tarjetas)
                    {

                        comm.CommandText = "select max(tac_id) from tbl_tarjetas_credito";
                        reader = comm.ExecuteReader();
                        if (reader.Read())
                            if (reader.IsDBNull(0)) tcid = 1; else tcid = reader.GetInt32(0) + 1;
                        if (reader != null)
                        {
                            reader.Close();
                            reader.Dispose();
                        }

                        comm.CommandText = "insert into tbl_tarjetas_credito(tac_id, tac_uin_id, tac_name, tac_credit_card, tac_exp_date, tac_ccv) values (@id, @uinID, @name, @creditcard, @expdate, @ccv)";
                        comm.Parameters.Add("@id", SqlDbType.Int).Value = tcid;
                        comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                        comm.Parameters.Add("@name", SqlDbType.VarChar).Value = tarjeta.name.ToUpper();
                        comm.Parameters.Add("@creditcard", SqlDbType.VarChar).Value = tarjeta.number;
                        comm.Parameters.Add("@expdate", SqlDbType.VarChar).Value = tarjeta.expdate;
                        comm.Parameters.Add("@ccv", SqlDbType.Int).Value = tarjeta.ccv;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();

                    }
                }

            }
            else
            { // usuario existente solo hago update de los rows
                string pass = "";
                if (usuario.pss != "" && usuario.pss != null)
                    pass = "usu_contrasena=@pass,";
                comm.CommandText = " update tbl_usuario set " + pass + " usu_nombre=@nombre, usu_apellido=@apellido, usu_alias=@alias, usu_compania=@compania, usu_email=@email, usu_title=@title, usu_logo=@logo  where  usu_id=@usuID ";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                if (pass != "")
                    comm.Parameters.Add("@pass", SqlDbType.VarChar).Value = usuario.pss;
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = usuario.nombre.ToUpper();
                comm.Parameters.Add("@apellido", SqlDbType.VarChar).Value = usuario.apellido.ToUpper();
                comm.Parameters.Add("@alias", SqlDbType.VarChar).Value = usuario.alias.ToUpper();
                comm.Parameters.Add("@compania", SqlDbType.VarChar).Value = usuario.compania.ToUpper();
                comm.Parameters.Add("@email", SqlDbType.VarChar).Value = usuario.email;
                comm.Parameters.Add("@title", SqlDbType.VarChar).Value = usuario.title.ToUpper();
                comm.Parameters.Add("@logo", SqlDbType.VarChar).Value = usuario.logo.ToUpper();
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                /*comm.CommandText = "delete from tbl_usuario_perfil where upe_usu_id=@usuID and upe_per_id=@perID";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                comm.Parameters.Add("@perID", SqlDbType.VarChar).Value = int.Parse(usuario.perfil);
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_usuario_perfil (upe_usu_id, upe_per_id, upe_estado) values (@usuID, @perID, 1)";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                comm.Parameters.Add("@perID", SqlDbType.VarChar).Value = int.Parse(usuario.perfil);
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();*/

                int uinID = 1;
                int tcid = 1;
                foreach (RE_Direccion direccion in usuario.direcciones)
                {

                    if (direccion.uinID == 0)
                    {
                        comm.CommandText = "select max(uin_id) from tbl_usuario_informacion";
                        reader = comm.ExecuteReader();
                        if (reader.Read())
                            if (reader.IsDBNull(0)) uinID = 1; else uinID = reader.GetInt32(0) + 1;
                        if (reader != null)
                        {
                            reader.Close();
                            reader.Dispose();
                        }
                        comm.CommandText = "insert into tbl_usuario_informacion(uin_id, uin_usu_id, uin_st_number, uin_st_add, uin_city, uin_state, uin_zip, uin_tel_oficina, uin_tel_celular, uin_fax, uin_tii_id) values (@uinID, @usuID, @stnum, @stadd, @city, @state, @zip, @telof, @telcel, @fax, @tipo)";
                        comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                        comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                        comm.Parameters.Add("@stnum", SqlDbType.VarChar).Value = direccion.stnumber.ToUpper();
                        comm.Parameters.Add("@stadd", SqlDbType.VarChar).Value = direccion.stadd.ToUpper();
                        comm.Parameters.Add("@city", SqlDbType.VarChar).Value = direccion.city.ToUpper();
                        comm.Parameters.Add("@state", SqlDbType.VarChar).Value = direccion.state.ToUpper();
                        comm.Parameters.Add("@zip", SqlDbType.VarChar).Value = direccion.zip;
                        comm.Parameters.Add("@telof", SqlDbType.VarChar).Value = direccion.telof;
                        comm.Parameters.Add("@telcel", SqlDbType.VarChar).Value = direccion.telcel;
                        comm.Parameters.Add("@fax", SqlDbType.VarChar).Value = direccion.fax;
                        comm.Parameters.Add("@tipo", SqlDbType.Int).Value = direccion.tipodir;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                    else
                    {
                        comm.CommandText = "update tbl_usuario_informacion set uin_usu_id=@usuID, uin_st_number=@stnum, uin_st_add=@stadd, uin_city=@city, uin_state=@state, uin_zip=@zip, uin_tel_oficina=@telof, uin_tel_celular=@telcel, uin_fax=@fax, uin_tii_id=@tipo where uin_id=@uinID";
                        comm.Parameters.Add("@uinID", SqlDbType.Int).Value = direccion.uinID;
                        comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                        comm.Parameters.Add("@stnum", SqlDbType.VarChar).Value = direccion.stnumber.ToUpper();
                        comm.Parameters.Add("@stadd", SqlDbType.VarChar).Value = direccion.stadd.ToUpper();
                        comm.Parameters.Add("@city", SqlDbType.VarChar).Value = direccion.city.ToUpper();
                        comm.Parameters.Add("@state", SqlDbType.VarChar).Value = direccion.state.ToUpper();
                        comm.Parameters.Add("@zip", SqlDbType.VarChar).Value = direccion.zip;
                        comm.Parameters.Add("@telof", SqlDbType.VarChar).Value = direccion.telof;
                        comm.Parameters.Add("@telcel", SqlDbType.VarChar).Value = direccion.telcel;
                        comm.Parameters.Add("@fax", SqlDbType.VarChar).Value = direccion.fax;
                        comm.Parameters.Add("@tipo", SqlDbType.Int).Value = direccion.tipodir;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                    /*foreach (RE_CreditCard tarjeta in direccion.tarjetas)
                    {
                        if (tarjeta.tacID == 0)
                        {
                            comm.CommandText = "select max(tac_id) from tbl_tarjetas_credito";
                            reader = comm.ExecuteReader();
                            if (reader.Read())
                                if (reader.IsDBNull(0)) tcid = 1; else tcid = reader.GetInt32(0) + 1;
                            if (reader != null)
                            {
                                reader.Close();
                                reader.Dispose();
                            }

                            comm.CommandText = "insert into tbl_tarjetas_credito(tac_id, tac_uin_id, tac_name, tac_credit_card, tac_exp_date, tac_ccv) values (@id, @uinID, @name, @creditcard, @expdate, @ccv)";
                            comm.Parameters.Add("@id", SqlDbType.Int).Value = tcid;
                            comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                            comm.Parameters.Add("@name", SqlDbType.VarChar).Value = tarjeta.name.ToUpper();
                            comm.Parameters.Add("@creditcard", SqlDbType.VarChar).Value = tarjeta.number;
                            comm.Parameters.Add("@expdate", SqlDbType.VarChar).Value = tarjeta.expdate;
                            comm.Parameters.Add("@ccv", SqlDbType.Int).Value = tarjeta.ccv;
                            comm.ExecuteNonQuery();
                            comm.Parameters.Clear();
                        } else {
                            comm.CommandText = "update tbl_tarjetas_credito set tac_uin_id=@uinID, tac_name=@name, tac_credit_card=@creditcard, tac_exp_date=@expdate, tac_ccv=@ccv where tac_id=@id";
                            comm.Parameters.Add("@id", SqlDbType.Int).Value = tarjeta.tacID;
                            comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                            comm.Parameters.Add("@name", SqlDbType.VarChar).Value = tarjeta.name.ToUpper();
                            comm.Parameters.Add("@creditcard", SqlDbType.VarChar).Value = tarjeta.number;
                            comm.Parameters.Add("@expdate", SqlDbType.VarChar).Value = tarjeta.expdate;
                            comm.Parameters.Add("@ccv", SqlDbType.Int).Value = tarjeta.ccv;
                            comm.ExecuteNonQuery();
                            comm.Parameters.Clear();
                        }

                    }*/
                }
            }
            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_User getUsuariosData(string userID, int tipo) {
        RE_User result = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            if (tipo == 0)
            {
                comm.CommandText = " SELECT tbl_usuario.usu_id, tbl_usuario.usu_estado, tbl_usuario.usu_fecha_creacion, tbl_usuario.usu_alias, tbl_usuario.usu_nombre, tbl_usuario.usu_apellido, tbl_usuario.usu_compania, tbl_usuario.usu_email, tbl_usuario.usu_title, tbl_usuario.usu_where, tbl_usuario.usu_where2, tbl_usuario.usu_permiso, tbl_usuario.usu_restricted, tbl_usuario.usu_logo,  tbl_usuario.usu_where3, SUM(tbl_perfil_aplicacion.ape_permiso) AS Expr1, SUM(tbl_perfil_aplicacion.ape_restricted), tbl_usuario_perfil.upe_group   FROM  tbl_usuario INNER JOIN tbl_usuario_perfil ON tbl_usuario.usu_id = tbl_usuario_perfil.upe_usu_id INNER JOIN  tbl_perfil ON tbl_usuario_perfil.upe_per_id = tbl_perfil.per_id INNER JOIN  tbl_perfil_aplicacion ON tbl_perfil.per_id = tbl_perfil_aplicacion.ape_per_id WHERE  (tbl_usuario.usu_id = @usuID) GROUP BY tbl_usuario.usu_id, tbl_usuario.usu_estado, tbl_usuario.usu_fecha_creacion, tbl_usuario.usu_alias, tbl_usuario.usu_nombre, tbl_usuario.usu_apellido, tbl_usuario.usu_compania, tbl_usuario.usu_email, tbl_usuario.usu_title, tbl_usuario.usu_where, tbl_usuario.usu_where2, tbl_usuario.usu_permiso, tbl_usuario.usu_restricted, tbl_usuario.usu_logo,  tbl_usuario.usu_where3, tbl_usuario_perfil.upe_group ";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = userID.Trim().ToUpper();

            }
            else {
                comm.CommandText = " SELECT tbl_usuario.usu_id, tbl_usuario.usu_estado, tbl_usuario.usu_fecha_creacion, tbl_usuario.usu_alias, tbl_usuario.usu_nombre, tbl_usuario.usu_apellido, tbl_usuario.usu_compania, tbl_usuario.usu_email, tbl_usuario.usu_title, tbl_usuario.usu_where, tbl_usuario.usu_where2, tbl_usuario.usu_permiso, tbl_usuario.usu_restricted, tbl_usuario.usu_logo,  tbl_usuario.usu_where3, SUM(tbl_perfil_aplicacion.ape_permiso) AS Expr1, SUM(tbl_perfil_aplicacion.ape_restricted), tbl_usuario_perfil.upe_group   FROM  tbl_usuario INNER JOIN tbl_usuario_perfil ON tbl_usuario.usu_id = tbl_usuario_perfil.upe_usu_id INNER JOIN  tbl_perfil ON tbl_usuario_perfil.upe_per_id = tbl_perfil.per_id INNER JOIN  tbl_perfil_aplicacion ON tbl_perfil.per_id = tbl_perfil_aplicacion.ape_per_id WHERE  (tbl_usuario.usu_alias = @alias) GROUP BY tbl_usuario.usu_id, tbl_usuario.usu_estado, tbl_usuario.usu_fecha_creacion, tbl_usuario.usu_alias, tbl_usuario.usu_nombre, tbl_usuario.usu_apellido, tbl_usuario.usu_compania, tbl_usuario.usu_email, tbl_usuario.usu_title, tbl_usuario.usu_where, tbl_usuario.usu_where2, tbl_usuario.usu_permiso, tbl_usuario.usu_restricted, tbl_usuario.usu_logo,  tbl_usuario.usu_where3, tbl_usuario_perfil.upe_group ";
                comm.Parameters.Add("@alias", SqlDbType.VarChar).Value = userID.Trim().ToUpper();
            
            }
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = new RE_User();
                if (!reader.IsDBNull(0)) result.userID = reader.GetString(0);
                if (!reader.IsDBNull(1)) result.estado = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) result.fechacreacion = reader.GetDateTime(2).ToShortDateString();
                if (!reader.IsDBNull(3)) result.alias = reader.GetString(3);
                if (!reader.IsDBNull(4)) result.nombre = reader.GetString(4);
                if (!reader.IsDBNull(5)) result.apellido = reader.GetString(5);
                if (!reader.IsDBNull(6)) result.compania = reader.GetString(6);
                if (!reader.IsDBNull(7)) result.email = reader.GetString(7);
                if (!reader.IsDBNull(8)) result.title = reader.GetString(8);
                if (!reader.IsDBNull(9)) result.where = reader.GetString(9);
                if (!reader.IsDBNull(10)) result.where2 = reader.GetString(10);
                if (!reader.IsDBNull(11)) result.permisouser = reader.GetInt64(11);
                if (!reader.IsDBNull(12)) result.restricted = reader.GetInt64(12);
                if (!reader.IsDBNull(13)) result.logo = reader.GetString(13);
                if (!reader.IsDBNull(14)) result.where3 = reader.GetString(14);
                if (!reader.IsDBNull(15)) result.permiso = long.Parse(reader.GetValue(15).ToString());
                if (!reader.IsDBNull(16)) result.restrictedrol = long.Parse(reader.GetValue(16).ToString());
                if (!reader.IsDBNull(17)) result.group = reader.GetString(17);
                result.existe = 1;
                result.direcciones = GetDirecciones(result.userID);
                result.companies = GetCompaniesUser(result.userID);
                result.accounts = GetAccountsUser(result.userID);
                result.counties = GetCountiesUser(result.userID);
                result.states = GetStatesUser(result.userID);
                result.perfiles = GetPerfilUser(result.userID);
                result.prices = GetPricesUser(result.userID);
                result.permisos = GetPermisosUser(result.userID);
                
            }
        }
        catch (Exception e) {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetStatesUser(string userID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT  ust_st_id FROM  tbl_usuario_state WHERE  (ust_usu_id = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value = userID.ToUpper();
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//state id

                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
   
    private static ArrayList GetCountiesUser(string userID)
    {
         ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = " SELECT ucou_county_id FROM tbl_usuario_county WHERE  (upper(ucou_usu_id) = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value= userID.ToUpper();
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//COUNTY id
              
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetPricesUser(string userID)
    {

        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT  upr_prod_id, upr_precio FROM  tbl_usuario_precio WHERE     (UPPER(upr_usu_id) = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value = userID.ToUpper();
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//product id
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetFloat(1).ToString();// precio
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetPerfilUser(string userID)
    {

        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_usuario_perfil.upe_per_id, tbl_perfil.per_nombre FROM  tbl_usuario_perfil INNER JOIN tbl_perfil ON tbl_usuario_perfil.upe_per_id = tbl_perfil.per_id WHERE   (tbl_usuario_perfil.upe_estado = 1) AND (upper(tbl_usuario_perfil.upe_usu_id) = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value = userID.ToUpper();
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//perfil id
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetString(1);// perfil nombre
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetPermisosUser(string userID)
    {

        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = " SELECT   usuario_permiso.usu_id, usuario_permiso.papp_id, usuario_permiso.permiso, usuario_permiso.restricted   FROM   tbl_usuario INNER JOIN   usuario_permiso ON tbl_usuario.usu_id = usuario_permiso.usu_id   WHERE   (tbl_usuario.usu_estado = 1) AND (UPPER(usuario_permiso.usu_id) = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value = userID.ToUpper();
            reader = comm.ExecuteReader();


            RE_GenericBean2 permisos;
            while (reader.Read())
            {
                permisos = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) permisos.strC1 = reader.GetString(0);//usu_id
                if (!reader.IsDBNull(1)) permisos.intC1 = reader.GetInt32(1);//papp_id
                if (!reader.IsDBNull(2)) permisos.int64C1 = reader.GetInt64(2);//permiso
                if (!reader.IsDBNull(3)) permisos.int64C2 = reader.GetInt64(3);//restricted
                result.Add(permisos);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetAccountsUser(string userID)
    {
        
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_usuario_account.uacc_acc_id, tbl_account.acc_nombre, tbl_account.acc_initial FROM   tbl_usuario_account INNER JOIN  tbl_account ON tbl_usuario_account.uacc_acc_id = tbl_account.acc_id WHERE  (tbl_usuario_account.uacc_usu_id = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value= userID;
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//account id
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetString(1);// account nombre
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetCompaniesUser(string userID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_usuario_company.uco_com_id, tbl_compania.com_name FROM tbl_usuario_company INNER JOIN   tbl_compania ON tbl_usuario_company.uco_com_id = tbl_compania.com_id WHERE     (tbl_usuario_company.uco_usu_id = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value= userID;
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//company id
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetString(1);//company name
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getUsuariosbyPerfil(int perfilID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_usuario.usu_id, tbl_usuario.usu_alias, tbl_usuario.usu_nombre, tbl_usuario.usu_apellido, tbl_usuario.usu_compania, tbl_usuario.usu_email,tbl_perfil.per_nombre FROM tbl_usuario INNER JOIN  tbl_usuario_perfil ON tbl_usuario.usu_id = tbl_usuario_perfil.upe_usu_id INNER JOIN tbl_perfil ON tbl_usuario_perfil.upe_per_id = tbl_perfil.per_id WHERE  (tbl_usuario_perfil.upe_per_id = @perfilID) AND (tbl_usuario.usu_estado = 1) order by tbl_usuario.usu_id";
            comm.Parameters.Add("@perfilID", SqlDbType.Int).Value = perfilID;
            reader = comm.ExecuteReader();

            RE_User user;
            while (reader.Read())
            {
                user = new RE_User();
                if (!reader.IsDBNull(0)) user.userID = reader.GetString(0); 
                if (!reader.IsDBNull(1)) user.alias = reader.GetString(1);
                if (!reader.IsDBNull(2)) user.nombre = reader.GetString(2);
                if (!reader.IsDBNull(3)) user.apellido = reader.GetString(3);
                if (!reader.IsDBNull(4)) user.compania = reader.GetString(4);
                if (!reader.IsDBNull(5)) user.email = reader.GetString(5);
                if (!reader.IsDBNull(6)) user.perfil = reader.GetString(6);
                result.Add(user);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getInactiveUsers(int value)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_usuario.usu_id, tbl_usuario.usu_alias, tbl_usuario.usu_nombre, tbl_usuario.usu_apellido, tbl_usuario.usu_compania, tbl_usuario.usu_email,tbl_perfil.per_nombre, tbl_perfil.per_id FROM tbl_usuario INNER JOIN  tbl_usuario_perfil ON tbl_usuario.usu_id = tbl_usuario_perfil.upe_usu_id INNER JOIN tbl_perfil ON tbl_usuario_perfil.upe_per_id = tbl_perfil.per_id WHERE  (tbl_usuario.usu_estado = @estado) order by tbl_perfil.per_nombre,tbl_usuario.usu_id";
            comm.Parameters.Add("@estado", SqlDbType.Int).Value = value;
            reader = comm.ExecuteReader();

            RE_User user;
            while (reader.Read())
            {
                user = new RE_User();
                if (!reader.IsDBNull(0)) user.userID = reader.GetString(0);
                if (!reader.IsDBNull(1)) user.alias = reader.GetString(1);
                if (!reader.IsDBNull(2)) user.nombre = reader.GetString(2);
                if (!reader.IsDBNull(3)) user.apellido = reader.GetString(3);
                if (!reader.IsDBNull(4)) user.compania = reader.GetString(4);
                if (!reader.IsDBNull(5)) user.email = reader.GetString(5);
                if (!reader.IsDBNull(6)) user.perfil = reader.GetString(6);
                if (!reader.IsDBNull(7)) user.existe = reader.GetInt32(7);
                result.Add(user);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getPerfiles(int catalogo)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            // si catalogo=1 significa que unicamente muestro los perfiles marcados como catalogo
            if (catalogo == 1)
            {
                comm.CommandText = " SELECT tbl_perfil.per_id, tbl_perfil.per_nombre    FROM   tbl_perfil  WHERE  (tbl_perfil.per_catalogo = 1) and ()  ORDER BY tbl_perfil.per_nombre";
                
            }
            else
            {
                comm.CommandText = " SELECT tbl_perfil.per_id, tbl_perfil.per_nombre    FROM   tbl_perfil  WHERE  (tbl_perfil.per_catalogo = 0) ORDER BY tbl_perfil.per_nombre";
            }
            reader = comm.ExecuteReader();

            RE_GenericBean2 perfil;
            while (reader.Read())
            {
                perfil = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) perfil.intC1 = reader.GetInt32(0);//perfil_id
                if (!reader.IsDBNull(1)) perfil.strC1 = reader.GetString(1);//per nombre
                result.Add(perfil);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getPerfilesbyApp(int catalogo, int app)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            // si catalogo=1 significa que unicamente muestro los perfiles marcados como catalogo
            if (catalogo == 1)
            {
                comm.CommandText = "  SELECT DISTINCT tbl_perfil.per_id, tbl_perfil.per_nombre FROM  tbl_perfil INNER JOIN  tbl_perfil_aplicacion ON tbl_perfil.per_id = tbl_perfil_aplicacion.ape_per_id WHERE     (tbl_perfil_aplicacion.ape_apl_id = @app) AND (tbl_perfil.per_catalogo = 1) ORDER BY tbl_perfil.per_nombre  ";
                comm.Parameters.Add("@app",SqlDbType.Int).Value= app;
            }
            else
            {
                comm.CommandText = "  SELECT  DISTINCT tbl_perfil.per_id, tbl_perfil.per_nombre FROM  tbl_perfil INNER JOIN  tbl_perfil_aplicacion ON tbl_perfil.per_id = tbl_perfil_aplicacion.ape_per_id WHERE     (tbl_perfil_aplicacion.ape_apl_id = @app) AND (tbl_perfil.per_catalogo = 0) ORDER BY tbl_perfil.per_nombre   ";
                comm.Parameters.Add("@app", SqlDbType.Int).Value = app;
            }
            reader = comm.ExecuteReader();

            RE_GenericBean2 perfil;
            while (reader.Read())
            {
                perfil = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) perfil.intC1 = reader.GetInt32(0);//perfil_id
                if (!reader.IsDBNull(1)) perfil.strC1 = reader.GetString(1);//per nombre
                result.Add(perfil);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static ArrayList getPerfilesPermiso(int catalogo)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = " SELECT    tbl_perfil.per_id, tbl_perfil.per_nombre, tbl_perfil_aplicacion.ape_permiso, tbl_perfil_aplicacion.ape_restricted, tbl_perfil_aplicacion.ape_papp_id    FROM    tbl_perfil INNER JOIN  tbl_perfil_aplicacion ON tbl_perfil.per_id = tbl_perfil_aplicacion.ape_per_id    WHERE    (tbl_perfil.per_catalogo = @catalogo)   ORDER BY tbl_perfil.per_nombre";
            comm.Parameters.Add("@catalogo", SqlDbType.Int).Value = catalogo;
            reader = comm.ExecuteReader();

            RE_GenericBean2 perfil;
            while (reader.Read())
            {
                perfil = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) perfil.intC1 = reader.GetInt32(0);//perfil_id
                if (!reader.IsDBNull(1)) perfil.strC1 = reader.GetString(1);//per nombre
                if (!reader.IsDBNull(2)) perfil.int64C1 = reader.GetInt64(2);//ape_permiso
                if (!reader.IsDBNull(3)) perfil.int64C2 = reader.GetInt64(3);//ape_restricted
                if (!reader.IsDBNull(4)) perfil.intC2 = reader.GetInt32(4);//papp_id
                result.Add(perfil);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetDirecciones(string userID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select uin_id, uin_usu_id, uin_st_number, uin_st_add, uin_city, uin_state, uin_zip, uin_tel_oficina, uin_tel_celular, uin_fax, uin_tii_id from tbl_usuario_informacion where uin_usu_id=@usuID";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = userID.Trim().ToUpper();
            reader = comm.ExecuteReader();
            RE_Direccion dir = null;
            while (reader.Read())
            {
                dir = new RE_Direccion();
                
                if (!reader.IsDBNull(0)) dir.uinID = reader.GetInt32(0);//uin_id
                //                if (!reader.IsDBNull(1)) dir.usuID=reader.getstring;//usu_id
                if (!reader.IsDBNull(2)) dir.stnumber = reader.GetString(2);//stnumber
                if (!reader.IsDBNull(3)) dir.stadd = reader.GetString(3);//stadd
                if (!reader.IsDBNull(4)) dir.city = reader.GetString(4);//city
                if (!reader.IsDBNull(5)) dir.state = reader.GetString(5);//state
                if (!reader.IsDBNull(6)) dir.zip = reader.GetString(6);//zip
                if (!reader.IsDBNull(7)) dir.telof = reader.GetString(7);//oficina
                if (!reader.IsDBNull(8)) dir.telcel = reader.GetString(8);//telcellar
                if (!reader.IsDBNull(9)) dir.fax = reader.GetString(9);//fax
                if (!reader.IsDBNull(10)) dir.tipodir = reader.GetInt32(10);//tii
                dir.tarjetas = getTarjetas(dir.uinID); // obtengo las tarjetas asociadas a esta direccion
                result.Add(dir);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList getTarjetas(int uinID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select tac_id, tac_name, tac_credit_card, tac_exp_date, tac_ccv from tbl_tarjetas_credito where tac_uin_id=@uinID";
            comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
            reader = comm.ExecuteReader();
            RE_CreditCard cc = null;
            while (reader.Read())
            {
                cc=new RE_CreditCard();
                if (!reader.IsDBNull(0)) cc.tacID=reader.GetInt32(0);//tacID
                if (!reader.IsDBNull(0)) cc.name=reader.GetString(1);//name
                if (!reader.IsDBNull(0)) cc.number=reader.GetString(2);//number
                if (!reader.IsDBNull(0)) cc.expdate=reader.GetString(3);//expdate
                if (!reader.IsDBNull(0)) cc.ccv=reader.GetInt32(4);//ccv
                result.Add(cc);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static bool setLogoUser(string XMLUsuarioLogo)
    {
        bool result = false;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "update tbl_usuario set usu_logo='' where usu_id=@usuID";
         //   comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = UserID.Trim().ToUpper();
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();


        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static ArrayList getCompanies()
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select com_id, com_name, com_url, com_contacts, com_phone from tbl_compania where com_estado= 1 ORDER BY 2";
            reader = comm.ExecuteReader();

            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetString(1);
                if (!reader.IsDBNull(2)) company.strC2 = reader.GetString(2);
                if (!reader.IsDBNull(3)) company.strC3 = reader.GetString(3);
                if (!reader.IsDBNull(4)) company.strC4 = reader.GetString(4);

                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }



    internal static ArrayList getAccounts()
    {
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            SqlCommand comm = null;
            SqlDataReader reader = null;
            try
            {
                conn = OpenConnection();
                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = "select acc_id, acc_nombre, acc_initial from tbl_account where acc_estado= 1 ORDER BY 2";
                reader = comm.ExecuteReader();

                RE_GenericBean2 account;
                while (reader.Read())
                {
                    account = new RE_GenericBean2();
                    if (!reader.IsDBNull(0)) account.intC1 = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) account.strC1 = reader.GetString(1);
                    if (!reader.IsDBNull(2)) account.strC2 = reader.GetString(2);
                    result.Add(account);
                }
            }
            catch (Exception e)
            {
                string ruta = "c:\\TEMP\\archivo.txt";
                StreamWriter escritor = new StreamWriter(ruta, true);
                escritor.WriteLine(e.Message.ToString());
                escritor.Close();
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                if (comm != null)
                    comm.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return result;
        }
    }

    internal static ArrayList getCompany(string comp)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select com_id, com_name from tbl_compania where com_estado= 1 and com_id="+comp+" ORDER BY 2";
            reader = comm.ExecuteReader();

            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetString(1);
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    internal static ArrayList getAccount(string acc)
    {
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            SqlCommand comm = null;
            SqlDataReader reader = null;
            try
            {
                conn = OpenConnection();
                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = "select acc_id, acc_nombre, acc_initial from tbl_account where acc_estado= 1 and acc_id="+acc+" ORDER BY 2";
                reader = comm.ExecuteReader();

                RE_GenericBean2 account;
                while (reader.Read())
                {
                    account = new RE_GenericBean2();
                    if (!reader.IsDBNull(0)) account.intC1 = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) account.strC1 = reader.GetString(1);
                    if (!reader.IsDBNull(2)) account.strC2 = reader.GetString(2);
                    result.Add(account);
                }
            }
            catch (Exception e)
            {
                string ruta = "c:\\TEMP\\archivo.txt";
                StreamWriter escritor = new StreamWriter(ruta, true);
                escritor.WriteLine(e.Message.ToString());
                escritor.Close();
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                if (comm != null)
                    comm.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return result;
        }
    }


    internal static ArrayList getOpcionApp(int app_id)
    {//devuelve las opciones para una app en especifico
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            SqlCommand comm = null;
            SqlDataReader reader = null;
            try
            {
                conn = OpenConnection();
                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = " SELECT    tbl_pageapp.papp_id, tbl_option.opc_name, tbl_option.opc_permiso    FROM    tbl_pageapp INNER JOIN    tbl_option ON tbl_pageapp.papp_id = tbl_option.opc_pagina    WHERE    (tbl_option.opc_permiso = 1) AND (tbl_pageapp.papp_apl_id = @appid)   ORDER BY tbl_pageapp.papp_id";
                comm.Parameters.Add("@appid",SqlDbType.Int).Value = app_id;
                reader = comm.ExecuteReader();
                RE_GenericBean2 opcion;
                while (reader.Read())
                {
                    opcion = new RE_GenericBean2();
                    if (!reader.IsDBNull(0)) opcion.intC1 = reader.GetInt32(0);//id
                    if (!reader.IsDBNull(1)) opcion.strC1 = reader.GetString(1);//nombre
                    if (!reader.IsDBNull(2)) opcion.int64C1 = reader.GetInt64(2);//permiso
                    result.Add(opcion);
                }
            }
            catch (Exception e)
            {
                string ruta = "c:\\TEMP\\archivo.txt";
                StreamWriter escritor = new StreamWriter(ruta, true);
                escritor.WriteLine(e.Message.ToString());
                escritor.Close();
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                if (comm != null)
                    comm.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return result;
        }
    }
    internal static string getOpcionbyPage(int page)
    {//devuelve las opciones para una app en especifico
        
            SqlConnection conn = null; SqlConnection conn2 = null;
            SqlCommand comm = null;
           
            SqlDataReader reader = null;
           
            string xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?><options>";
            try
            {
                conn = OpenConnection();
                conn2 = OpenConnection();
                comm = new SqlCommand();
                comm.Connection = conn;

                 comm.CommandType = CommandType.Text;
                comm.CommandText = "SELECT tbl_pageapp.papp_name, tbl_option.opc_name, tbl_option.opc_id, tbl_option.opc_permiso FROM tbl_pageapp INNER JOIN tbl_option ON tbl_pageapp.papp_id = tbl_option.opc_pagina WHERE (tbl_pageapp.papp_id = @page)";
                comm.Parameters.Add("@page", SqlDbType.Int).Value = page;
                reader = comm.ExecuteReader();
                RE_GenericBean2 opcion;

                while (reader.Read())
                {
                    opcion = new RE_GenericBean2();
                    if (!reader.IsDBNull(0)) opcion.strC1 = reader.GetString(0);//page name
                    if (!reader.IsDBNull(1)) opcion.strC2 = reader.GetString(1);//nombre opcion
                    if (!reader.IsDBNull(2)) opcion.intC1 = reader.GetInt32(2);//id opcion
                    if (!reader.IsDBNull(3)) opcion.int64C1 = reader.GetInt64(3);//permiso
                   

                    xml += "<option><id>" + opcion.intC1 + "</id><name>" + opcion.strC2 + "</name><pagename>"+opcion.strC1+"</pagename><permit>" + opcion.int64C1 + "</permit>";
                  
                    xml += "</option>";
                   
                   
                }

                reader.Close();
                xml += "</options>";
               
            }
            catch (Exception ex)
            {
                xml = "";
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
             
                if (comm != null)
                    comm.Dispose();
             
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return xml; 

          
    }
    internal static bool InsertarCompany(RE_GenericBean2 company)
    {
         bool result = false;
        
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        
        try
        {


            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
        
            if (company!= null)
            {
                comm.CommandText = " MERGE  tbl_compania  ";
                comm.CommandText += " USING ( SELECT @id AS ID1) AS EX ON (EX.ID1 = tbl_compania.com_id) ";
                comm.CommandText += " WHEN MATCHED THEN ";
                comm.CommandText += " UPDATE SET   com_name=@comName, com_url=@comUrl, com_contacts=@comcontacts, com_phone=@comphone ";
                comm.CommandText += " WHEN NOT MATCHED THEN ";
                comm.CommandText += "insert  ( com_name, com_estado, com_url, com_contacts, com_phone) values (@comName,@estado,@comUrl, @comcontacts, @comphone);";
                comm.Parameters.Add("@id", SqlDbType.Int).Value = company.intC1;
                comm.Parameters.Add("@comName", SqlDbType.VarChar).Value = company.strC1;
                comm.Parameters.Add("@estado", SqlDbType.Int).Value = company.intC2;
                comm.Parameters.Add("@comUrl", SqlDbType.VarChar).Value = company.strC2;
                comm.Parameters.Add("@comcontacts", SqlDbType.VarChar).Value = company.strC3;
                comm.Parameters.Add("@comphone", SqlDbType.VarChar).Value = company.strC4;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                return true;

             }
        }
        catch (Exception e)
        {
            
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }
    internal static bool InsertarAccount(RE_GenericBean2 company)
    {
        bool result = false;

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {


            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            if (company != null)
            {
                comm.CommandText = "insert into tbl_account ( acc_nombre, acc_initial, acc_estado) values (@accName, @accinitial,@accestado)";
                comm.Parameters.Add("@accName", SqlDbType.VarChar).Value = company.strC1;
                comm.Parameters.Add("@accinitial" ,SqlDbType.VarChar).Value = company.strC2;
                comm.Parameters.Add("@accestado", SqlDbType.Int).Value = company.intC1;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                return true;

            }
        }
        catch (Exception e)
        {

            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }

    internal static bool InsertarTEmployee(RE_GenericBean2 temployee)
    {
        bool result = false;

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
      
        try
        {


            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
          
            int next_per_id=0;
            if (temployee != null)
            {
                comm.CommandText = "SELECT TOP (1) CAST(per_id AS int) AS perfil_id FROM  tbl_perfil ORDER BY perfil_id DESC";
                reader = comm.ExecuteReader();

                
                while (reader.Read())
                {
                    
                    if (!reader.IsDBNull(0)) next_per_id = reader.GetInt32(0);
                    next_per_id = next_per_id + 1;
                    string ruta = "c:\\TEMP\\archivo213ws.txt";
                    StreamWriter escritor = new StreamWriter(ruta, true);
                    escritor.WriteLine("Next_per_id= "+ next_per_id.ToString());
                    escritor.Close();
                    
                }
                reader.Close();
                reader.Dispose();
                if (next_per_id != 0)
                {

                    comm.CommandText = "insert into tbl_perfil (per_id, per_nombre, per_catalogo) values (@perid,@pernombre, @percatalogo)";
                    comm.Parameters.Add("@perid", SqlDbType.Int).Value = next_per_id;
                    comm.Parameters.Add("@pernombre", SqlDbType.VarChar).Value = temployee.strC1;
                    comm.Parameters.Add("@percatalogo", SqlDbType.Int).Value = temployee.intC1;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();

                    comm.CommandText = "insert into tbl_perfil_aplicacion (ape_per_id, ape_apl_id, ape_permiso, ape_restricted) values (@perid, 1, 128, 53637285016 )";
                    comm.Parameters.Add("@perid", SqlDbType.Int).Value = next_per_id;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                    return true;

                }
            }
        }
        catch (Exception e)
        {

            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString()+" "+e.StackTrace+" "+comm.CommandText);
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }

    internal static int delete(string tablename, string criterio, string valorcriterio, int intvalorcriterio, int type)
    {//borrar los registros de la tabla tablename  segun el criterio con el valor que recibe STRING ==1 int==0
         int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from "+tablename+"  where "+criterio+" =@criterio";
            if (type==1){
                comm.Parameters.Add("@criterio", SqlDbType.VarChar).Value = valorcriterio;

            }
            if (type == 0)
            {
                comm.Parameters.Add("@criterio", SqlDbType.Int).Value = intvalorcriterio;

            }

            comm.ExecuteNonQuery();
            result = 1;
        }
        catch (Exception ex) {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int setPerfilUsuario(string usuarioid, int[] matperfil, int count,int estado)
    {
       int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++ )
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = " INSERT INTO tbl_usuario_perfil (upe_usu_id, upe_per_id, upe_estado) VALUES (@usuid,@perfil,@estado) ";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@perfil", SqlDbType.Int).Value = matperfil[i];
                comm.Parameters.Add("@estado", SqlDbType.Int).Value = estado;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }

            if (matperfil[0] != 0)
            {
                ArrayList pages = new ArrayList();
                RE_GenericBean2 info;
                comm.CommandText = " SELECT   ape_papp_id, ape_permiso, ape_restricted   FROM   tbl_perfil_aplicacion   WHERE   (ape_apl_id = @apl_id) AND (ape_per_id = @per_id) ";
                comm.Parameters.Add("@apl_id", SqlDbType.Int).Value = 1;//aplicacion
                comm.Parameters.Add("@per_id", SqlDbType.Int).Value = matperfil[0];//perfil_id
                reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    info = new RE_GenericBean2();
                    if (!reader.IsDBNull(0)) info.intC1 = reader.GetInt32(0);//page_id
                    if (!reader.IsDBNull(1)) info.int64C1 = reader.GetInt64(1);//permit page
                    if (!reader.IsDBNull(2)) info.int64C2 = reader.GetInt64(2);//restrict page
                    pages.Add(info);
                }
                reader.Close();
                comm.Parameters.Clear();
                foreach (RE_GenericBean2 rg in pages)
                {
                    comm.CommandText = " UPDATE usuario_permiso SET permiso=@per, restricted=@res WHERE (usu_id=@usuario) AND (papp_id=@page_id) ";
                    comm.Parameters.Add("@per", SqlDbType.BigInt).Value = rg.int64C1;
                    comm.Parameters.Add("@res", SqlDbType.BigInt).Value = rg.int64C2;
                    comm.Parameters.Add("@usuario", SqlDbType.VarChar).Value = usuarioid;
                    comm.Parameters.Add("@page_id", SqlDbType.Int).Value = rg.intC1;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
            }
            result = 1;
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int setCompanyporUsuario(string usuarioid, int[] matcompany, int count)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++)
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_company (uco_usu_id, uco_com_id) values (@usuid,@company)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@company", SqlDbType.Int).Value = matcompany[i];
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
               
            }
            result = 1;
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int updatedata(string tablename, string columnanme, string criterio, string valorcriterio, int valor, string valorst, long valor64, int tipo)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

           
                comm.CommandType = CommandType.Text;
                comm.CommandText = "update tbl_usuario set "+columnanme+"= @valor where "+criterio+"= @valorcriterio";
                if (tipo == 2)
                {
                    comm.Parameters.Add("@valor", SqlDbType.BigInt).Value = valor64;
                }
                if(tipo==1){ 
                    comm.Parameters.Add("@valor", SqlDbType.VarChar).Value = valorst;
                }
                if(tipo==0){
                    comm.Parameters.Add("@valor", SqlDbType.Int).Value = valor;
                }
                comm.Parameters.Add("@valorcriterio", SqlDbType.VarChar).Value = valorcriterio;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                result = 1;
              
        }
             
        catch (Exception ex)
        {
            result = 0;
            string ruta = "c:\\TEMP\\archivohoy.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(DateTime.Now.ToString()+ ex.Message.ToString()+ ex.StackTrace);
            escritor.Close();
            
            
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int updateDataUsuPermiso(string columnanme, string usu_id, RE_GenericBean2 permiso)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            comm.CommandType = CommandType.Text;
            comm.CommandText = "update usuario_permiso set " + columnanme + "= @valor  where  (usu_id = @usuario) and (papp_id = @page)";
            comm.Parameters.Add("@valor", SqlDbType.BigInt).Value = permiso.int64C1;
            comm.Parameters.Add("@usuario", SqlDbType.VarChar).Value = usu_id;
            comm.Parameters.Add("@page", SqlDbType.Int).Value = permiso.intC1;
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();
            result = 1;

        }

        catch (Exception ex)
        {
            result = 0;
            string ruta = "c:\\TEMP\\archivohoy.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(DateTime.Now.ToString() + ex.Message.ToString() + ex.StackTrace);
            escritor.Close();


        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int setStateporUsuario(string usuarioid, int[] matstate, int count)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++)
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_state (ust_usu_id, ust_st_id) values (@usuid,@state)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@state", SqlDbType.Int).Value = matstate[i];
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
               
            }
            result = 1;
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int setPrecioporUsuario(string usuarioid, string[][] matriz, int count)
    {

        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++)
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_precio (upr_usu_id, upr_prod_id, upr_precio) values (@usuid,@product,@price)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@product", SqlDbType.Int).Value = matriz[i][0];
                comm.Parameters.Add("@price", SqlDbType.Real).Value = matriz[i][1];
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                
            }
            result = 1;
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int setCountyporUsuario(string usuarioid, int[] matriz, int count)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++)
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_county (ucou_usu_id, ucou_county_id) values (@usuid,@county)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@county", SqlDbType.Int).Value = matriz[i];
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = 1;
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }



    private static ArrayList GetCompanies(string userID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_compania.com_name, tbl_compania.com_id FROM  tbl_compania INNER JOIN  tbl_usuario_company ON tbl_compania.com_id = tbl_usuario_company.uco_com_id WHERE     (tbl_usuario_company.uco_usu_id = 'LUCIANAB')";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = userID.Trim().ToUpper();
            reader = comm.ExecuteReader();
            RE_GenericBean2 Companies = null;
            while (reader.Read())
            {
                Companies = new RE_GenericBean2();

                if (!reader.IsDBNull(0)) Companies.strC1 = reader.GetString(0);//company name
                if (!reader.IsDBNull(1)) Companies.intC1 = reader.GetInt32(1);//company id
                result.Add(Companies);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetAccounts(string userID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_compania.com_name, tbl_compania.com_id FROM  tbl_compania INNER JOIN  tbl_usuario_company ON tbl_compania.com_id = tbl_usuario_company.uco_com_id WHERE     (tbl_usuario_company.uco_usu_id = 'LUCIANAB')";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = userID.Trim().ToUpper();
            reader = comm.ExecuteReader();
            RE_GenericBean2 Companies = null;
            while (reader.Read())
            {
                Companies = new RE_GenericBean2();

                if (!reader.IsDBNull(0)) Companies.strC1 = reader.GetString(0);//company name
                if (!reader.IsDBNull(1)) Companies.intC1 = reader.GetInt32(1);//company id
                result.Add(Companies);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int  setAccountporUsuario(string usuarioid, int[] matcompany, int count)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++)
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_account (uacc_usu_id, uacc_acc_id) values (@usuid,@acc)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@acc", SqlDbType.Int).Value = matcompany[i];
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int updatePergilApp(int app, int perfilid, ArrayList array, string name)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            long[,] matriz = new long[2, 200];
            matriz = (long[,]) array[0];
            long[,] restricted= new long[2,200];
            restricted = (long[,]) array[1];

            for ( int i =0; i< 200; i++){
             
                 if (matriz[0, i] != 0)
                 {
                     int papp = int.Parse(matriz[0, i].ToString());
                     int papp2 = int.Parse(restricted[0, i].ToString());
                     long temp = matriz[1, i];
                     long res=  restricted[1,i];
                     if (papp == papp2)
                     {
                         comm.CommandType = CommandType.Text;
                         comm.CommandText = "select  ape_papp_id, ape_permiso, ape_restricted from  tbl_perfil_aplicacion   where  ape_per_id=@perfil and ape_apl_id=@app and ape_papp_id=@papp";
                         comm.Parameters.Add("@app", SqlDbType.VarChar).Value = app;
                         comm.Parameters.Add("@perfil", SqlDbType.VarChar).Value = perfilid;
                         comm.Parameters.Add("@papp", SqlDbType.Int).Value = papp;
                         reader = comm.ExecuteReader();
                         comm.Parameters.Clear();
                         int existe = 0;
                         while (reader.Read()) {
                             existe = 1;
                         }
                         comm.Parameters.Clear();
                         reader.Close();
                         if (existe == 1)
                         {

                             
                             comm.CommandText = "update tbl_perfil_aplicacion set ape_permiso= @permiso ,ape_restricted=@restricted, ape_papp_id=@papp  where  ape_per_id=@perfil and ape_apl_id=@app and ape_papp_id=@papp";
                             comm.Parameters.Add("@app", SqlDbType.VarChar).Value = app;
                             comm.Parameters.Add("@permiso", SqlDbType.BigInt).Value = temp;
                             comm.Parameters.Add("@perfil", SqlDbType.VarChar).Value = perfilid;
                             comm.Parameters.Add("@restricted", SqlDbType.BigInt).Value = res;
                             comm.Parameters.Add("@papp", SqlDbType.Int).Value = papp;
                             comm.ExecuteNonQuery();
                             comm.Parameters.Clear();
                         }
                         else {

                             comm.CommandText = "insert into tbl_perfil_aplicacion ( ape_permiso, ape_restricted, ape_papp_id, ape_per_id,ape_apl_id) values ( @permiso ,@restricted, @papp ,@perfil , @app)";
                             comm.Parameters.Add("@app", SqlDbType.VarChar).Value = app;
                             comm.Parameters.Add("@permiso", SqlDbType.BigInt).Value = temp;
                             comm.Parameters.Add("@perfil", SqlDbType.VarChar).Value = perfilid;
                             comm.Parameters.Add("@restricted", SqlDbType.BigInt).Value = res;
                             comm.Parameters.Add("@papp", SqlDbType.Int).Value = papp;
                             comm.ExecuteNonQuery();
                             comm.Parameters.Clear();
                         
                         
                         }
                     }
                 }

             }
            comm.CommandText = "update tbl_perfil set per_nombre= @name where per_id=@perfil";
            comm.Parameters.Add("@name", SqlDbType.VarChar).Value= name;
            comm.Parameters.Add("@perfil", SqlDbType.VarChar).Value = perfilid;
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            
            result = 1;

        }

        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result; 
    }

    internal static bool InsertarPerfil(RE_GenericBean2 perfil)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        SqlConnection conn2 = null;
        SqlCommand comm2 = null;
        SqlDataReader reader2 = null;
        conn2 = OpenConnection();
        transaction = conn.BeginTransaction();

        try
        {


            comm = new SqlCommand();
            comm.Connection = conn;
            comm.Transaction = transaction;
            comm.CommandType = CommandType.Text;
            int nextperfilid=0;
            if (perfil != null)
            {
                comm.CommandText = "SELECT MAX(per_id) AS Expr1 FROM tbl_perfil";
                reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) nextperfilid = reader.GetInt32(0);//perfil_id
                }

                nextperfilid= nextperfilid+1;
                reader.Close();
                if (nextperfilid != 0)
                {
                    comm.CommandText = "insert into tbl_perfil ( per_id, per_nombre, per_catalogo) values (@perid, @name, 1)";
                    comm.Parameters.Add("@perid", SqlDbType.Int).Value = nextperfilid;
                    comm.Parameters.Add("@name", SqlDbType.VarChar).Value = perfil.strC1;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();


                    conn2 = OpenConnection();
                    comm2 = new SqlCommand();
                    comm2.Connection = conn2;


                    comm2.CommandType = CommandType.Text;
                    comm2.CommandText = " SELECT    tbl_pageapp.papp_id, tbl_pageapp.papp_name, tbl_option.opc_permiso    FROM   tbl_pageapp INNER JOIN  tbl_option ON tbl_pageapp.papp_id = tbl_option.opc_pagina    WHERE    (tbl_option.opc_permiso = 1) AND (tbl_pageapp.papp_apl_id = @appid)   ORDER BY tbl_pageapp.papp_id";
                    comm2.Parameters.Add("@appid", SqlDbType.Int).Value = perfil.intC1;
                    reader2 = comm2.ExecuteReader();
                    int papp_id;

                    while (reader2.Read())
                    {
                        papp_id = 0;
                        if (!reader2.IsDBNull(0)) papp_id = reader2.GetInt32(0);//papp_id
                        if (papp_id > 0)
                        {
                            comm.CommandText = "insert into tbl_perfil_aplicacion (ape_per_id, ape_apl_id, ape_permiso, ape_papp_id) values (@perid, @appid, @permiso, @papp_id)";
                            comm.Parameters.Add("@perid", SqlDbType.Int).Value = nextperfilid;
                            comm.Parameters.Add("@appid", SqlDbType.Int).Value = perfil.intC1;
                            comm.Parameters.Add("@permiso", SqlDbType.BigInt).Value = perfil.int64C1;
                            comm.Parameters.Add("@papp_id", SqlDbType.Int).Value = papp_id;

                            comm.ExecuteNonQuery();
                            comm.Parameters.Clear();
                        }
                    }

                    reader2.Close();
                    
                    transaction.Commit();
                    return true;
                }
            }
        }
        catch (Exception e)
        {
            transaction.Rollback();
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }
    internal static bool InsertarPage(RE_GenericBean2 opcion)
    {
        bool result = false;
        ArrayList perfiles = new ArrayList();
        SqlConnection conn = null;
        SqlTransaction transaction = null;
        SqlCommand comm = null;
        SqlDataReader reader = null, reader2 = null;
        conn = OpenConnection();
       

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            transaction = conn.BeginTransaction();

            DataSet opciones = DB2.getPagesds(opcion.intC1);
            decimal maxper = 1;
            
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (opcion != null)
            {
                comm.CommandText = " SELECT MAX(papp_id) AS Expr1 FROM tbl_pageapp    WHERE    (papp_apl_id = @appid)";
                comm.Parameters.Add("@appid", SqlDbType.Int).Value = opcion.intC1;
                reader = comm.ExecuteReader();
                int next=0;
                while (reader.Read())
                {
                   if (!reader.IsDBNull(0)) next = reader.GetInt32(0);//papp_id
                }

                reader.Close();
                comm.Parameters.Clear();
                next = next + 1;

                comm.CommandText = " insert into tbl_pageapp (papp_id, papp_name, papp_apl_id, papp_path_image) values (@id,@nombre,@appid,@path) ";
                comm.Parameters.Add("@appid", SqlDbType.Int).Value = opcion.intC1;
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = opcion.strC1;
                comm.Parameters.Add("@id", SqlDbType.Int).Value = next;
                comm.Parameters.Add("@path", SqlDbType.VarChar).Value = "noIcon.png";
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                    comm.CommandText = " INSERT INTO tbl_option    (opc_pagina, opc_name, opc_permiso)    VALUES    (@pagina,@name,@permiso)";
                    comm.Parameters.Add("@pagina", SqlDbType.Int).Value =  next;
                    comm.Parameters.Add("@name", SqlDbType.VarChar).Value = opcion.strC1;
                    comm.Parameters.Add("@permiso", SqlDbType.BigInt).Value = opcion.int64C1;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();

                comm.CommandText = " SELECT   MAX(orden) AS Expr1   FROM   tbl_page_n1   WHERE   (pg1_apl_id = @appid) ";
                comm.Parameters.Add("@appid", SqlDbType.Int).Value = opcion.intC1;
                reader = comm.ExecuteReader();
                int orden = 0;
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) orden = reader.GetInt32(0);//orden
                }

                reader.Close();
                comm.Parameters.Clear();
                orden = orden + 1;

                    comm.CommandText = " INSERT INTO tbl_page_n1 (pg1_apl_id, pg1_papp_id, pg1_type, orden)  VALUES     (@app, @papp_id, @type, @orden) ";
                    comm.Parameters.Add("@app", SqlDbType.Int).Value = opcion.intC1;
                    comm.Parameters.Add("@papp_id", SqlDbType.Int).Value = next;
                    comm.Parameters.Add("@type", SqlDbType.Int).Value = 1;
                    comm.Parameters.Add("@orden", SqlDbType.Int).Value = orden;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();

                comm.CommandText = " INSERT INTO tbl_section_page (section_name, sec_papp_id, orden)  VALUES   (@name, @papp_id, @orden) ";
                comm.Parameters.Add("@name", SqlDbType.VarChar).Value = "General";
                comm.Parameters.Add("@papp_id", SqlDbType.Int).Value = next;
                comm.Parameters.Add("@orden", SqlDbType.Int).Value = 1;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = " SELECT   tbl_perfil.per_id   FROM   tbl_perfil INNER JOIN   tbl_perfil_aplicacion ON tbl_perfil.per_id = tbl_perfil_aplicacion.ape_per_id   WHERE   (tbl_perfil_aplicacion.ape_apl_id = @apl_id)   GROUP BY tbl_perfil.per_id";
                comm.Parameters.Add("@apl_id", SqlDbType.Int).Value = opcion.intC1;
                reader2 = comm.ExecuteReader();
                int perID = 0;
                while (reader2.Read())
                {
                    if (!reader2.IsDBNull(0)) perID = reader2.GetInt32(0);//ape_per_id
                    perfiles.Add(perID);
                }
                reader2.Close();
                comm.Parameters.Clear();

                foreach (int per in perfiles)
                {
                    comm.CommandText = " INSERT INTO tbl_perfil_aplicacion   (ape_per_id, ape_apl_id, ape_permiso, ape_restricted, ape_papp_id)   VALUES   (@perfil, @apl_id, @permiso, @restricted, @papp_id)";
                    comm.Parameters.Add("@perfil", SqlDbType.Int).Value = per;
                    comm.Parameters.Add("@apl_id", SqlDbType.Int).Value = opcion.intC1;
                    comm.Parameters.Add("@permiso", SqlDbType.BigInt).Value = 0;
                    comm.Parameters.Add("@restricted", SqlDbType.BigInt).Value = 1;
                    comm.Parameters.Add("@papp_id", SqlDbType.Int).Value = next;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
   
                result= true;
                transaction.Commit();
                updateUsuario_Permiso(next, 0, 1);
                }
        }
        catch (Exception e)
        {
            transaction.Rollback();
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            result= false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }
    internal static int updatePageApp(int app, int opcionid, string name)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlTransaction transaction = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            transaction = conn.BeginTransaction();

            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;

            comm.CommandText = " update tbl_pageapp set papp_name= @name  where  papp_id=@opcionid and papp_apl_id=@app ";
            comm.Parameters.Add("@app", SqlDbType.VarChar).Value = app;
            comm.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
            comm.Parameters.Add("@opcionid", SqlDbType.VarChar).Value = opcionid;
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            comm.CommandText = " UPDATE    tbl_option   SET   opc_name = @pagename   WHERE   (opc_pagina = @pageid) AND (opc_permiso = @permiso) ";
            comm.Parameters.Add("@pagename", SqlDbType.VarChar).Value = name;
            comm.Parameters.Add("@pageid", SqlDbType.Int).Value = opcionid;
            comm.Parameters.Add("@permiso", SqlDbType.VarChar).Value = 1;
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            result = 1;
            transaction.Commit();
        }

        catch (Exception ex)
        {
            result = 0;
            transaction.Rollback();   
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getPerfilPerRes(int perid, string app)
    {
        RE_GenericBean2 perfil = null;
        ArrayList perfilPerRes = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = " SELECT     ape_per_id, ape_permiso, ape_restricted, ape_papp_id   FROM   tbl_perfil_aplicacion   WHERE   (ape_per_id = @perid) AND (ape_apl_id = @app)";
            comm.Parameters.Add("@perid", SqlDbType.Int).Value = perid;
            comm.Parameters.Add("@app", SqlDbType.Int).Value = app;

            reader = comm.ExecuteReader();
            perfilPerRes = new ArrayList();

            while (reader.Read())
            {
                perfil = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) perfil.intC1 = reader.GetInt32(0);//perfil_id
                if (!reader.IsDBNull(1)) perfil.int64C1 = reader.GetInt64(1);//permiso
                if (!reader.IsDBNull(2)) perfil.int64C2 = reader.GetInt64(2);//restricted
                if (!reader.IsDBNull(3)) perfil.intC2 = reader.GetInt32(3);//papp_id
                perfilPerRes.Add(perfil);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(DateTime.Now.ToString() + e.Message.ToString() + e.StackTrace);
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return perfilPerRes;
    }
    internal static int updateUserPerRes(int app, int perfilid,ArrayList array)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlConnection conn2 = null;
        SqlCommand comm = null;
        SqlCommand comm2 = null;
        SqlDataReader reader = null;
        SqlDataReader reader2 = null;
      
        SqlTransaction transaction2 = null;
        try
        {
            conn = OpenConnection();
            conn2 = OpenConnection();
            comm = new SqlCommand();
            comm2 = new SqlCommand();
            comm.Connection = conn;
            comm2.Connection = conn2;
           
            transaction2 = conn2.BeginTransaction();
            comm2.Transaction = transaction2;         
           
            long[,] permiso= (long[,])array[0];
            long[,] restricted = (long[,])array[1];

            for (int i = 0; i < 200; i++)
            {
                
                int pageid = int.Parse(permiso[0, i].ToString());
                int pageid2 = int.Parse(restricted[0, i].ToString());
                if (pageid!=0 && pageid==pageid2)
                {
                        comm.CommandType = CommandType.Text;
                        //actualizo las restricciones para cada usuario del prfil
                        comm.CommandText = "SELECT  tbl_usuario.usu_id, tbl_perfil.per_nombre, tbl_perfil_aplicacion.ape_per_id, tbl_pageapp.papp_id, tbl_perfil_aplicacion.ape_permiso, tbl_perfil_aplicacion.ape_restricted FROM   tbl_perfil_aplicacion INNER JOIN  tbl_aplicacion ON tbl_perfil_aplicacion.ape_apl_id = tbl_aplicacion.apl_id INNER JOIN  tbl_perfil ON tbl_perfil_aplicacion.ape_per_id = tbl_perfil.per_id INNER JOIN tbl_usuario_perfil INNER JOIN  tbl_usuario ON tbl_usuario_perfil.upe_usu_id = tbl_usuario.usu_id ON tbl_perfil.per_id = tbl_usuario_perfil.upe_per_id INNER JOIN tbl_pageapp ON tbl_perfil_aplicacion.ape_papp_id = tbl_pageapp.papp_id AND tbl_aplicacion.apl_id = tbl_pageapp.papp_apl_id WHERE        (tbl_perfil_aplicacion.ape_apl_id = @app) AND (tbl_usuario.usu_estado = 1) AND (tbl_perfil.per_id = @perfilid) AND (tbl_pageapp.papp_id = @pageid)";
                        comm.Parameters.Add("@app", SqlDbType.VarChar).Value = app;
                        comm.Parameters.Add("@perfilid", SqlDbType.VarChar).Value = perfilid;
                        comm.Parameters.Add("@pageid", SqlDbType.VarChar).Value = pageid;
                        reader = comm.ExecuteReader();
                        comm.Parameters.Clear();

                        while (reader.Read())
                        {

                            string userid = "";
                            if (!reader.IsDBNull(0)) userid = reader.GetString(0);//usuid
                            long per = long.Parse(permiso[1, i].ToString());
                            long res = long.Parse(restricted[1, i].ToString());

                            comm2.CommandText = "SELECT  usu_id, papp_id FROM   usuario_permiso WHERE  (papp_id = @pageid) AND (usu_id=@usuid) ";
                            comm2.Parameters.Add("@usuid", SqlDbType.VarChar).Value = userid;
                            comm2.Parameters.Add("@pageid", SqlDbType.VarChar).Value = pageid;
                           
                            reader2 = comm2.ExecuteReader();
                            comm2.Parameters.Clear();
                            int existe = 0;
                            while (reader2.Read())
                            {

                                existe = 1;
                            }
                            reader2.Close();
                            if (existe == 1)
                            {
                                comm2.CommandText = "update usuario_permiso set  permiso=@permiso, restricted=@restricted where  usu_id=@usuid and papp_id= @pageid  ";
                                comm2.Parameters.Add("@usuid", SqlDbType.VarChar).Value = userid;
                                comm2.Parameters.Add("@pageid", SqlDbType.VarChar).Value = pageid;
                                comm2.Parameters.Add("@permiso", SqlDbType.BigInt).Value = per;
                                comm2.Parameters.Add("@restricted", SqlDbType.BigInt).Value = res;
                                comm2.ExecuteNonQuery();
                                comm2.Parameters.Clear();

                            }
                            else {
                                comm2.CommandText = "insert into usuario_permiso (usu_id, papp_id,  permiso, restricted) values ( @usuid, @pageid, @permiso,@restricted  ) ";
                                comm2.Parameters.Add("@usuid", SqlDbType.VarChar).Value = userid;
                                comm2.Parameters.Add("@pageid", SqlDbType.VarChar).Value = pageid;
                                comm2.Parameters.Add("@permiso", SqlDbType.BigInt).Value = per;
                                comm2.Parameters.Add("@restricted", SqlDbType.BigInt).Value = res;
                                comm2.ExecuteNonQuery();
                                comm2.Parameters.Clear();
                            
                            }

                        

                        }
                        reader.Close();
                }
            }
           
            transaction2.Commit();
            result = 1;
        }
        catch (Exception e)
        {
           
            transaction2.Rollback();
            return 0;
        } finally{
            if (conn!= null ){  conn.Dispose();  }
            if (conn2!= null ){  conn.Dispose();  }
            if (reader!= null ){  reader.Dispose();  }
            if (reader2 != null) { reader2.Dispose(); }
            if (comm != null) { comm.Dispose(); }
            if (comm2 != null) { comm2.Dispose(); }
        
        }

        return result;
    }

    internal static int updateUsuario_Permiso(int page, long permiso, long restricted)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlConnection conn2 = null;
        SqlCommand comm = null;
        SqlCommand comm2 = null;
        SqlDataReader reader = null;
        SqlDataReader reader2 = null;
      
        SqlTransaction transaction2 = null;
        try
        {
            conn = OpenConnection();
            conn2 = OpenConnection();
            comm = new SqlCommand();
            comm2 = new SqlCommand();
            comm.Connection = conn;
            comm2.Connection = conn2;
           
            transaction2 = conn2.BeginTransaction();
            comm2.Transaction = transaction2;

            if (page != 0)
            {
                comm.CommandType = CommandType.Text;
                //actualizo las restricciones para cada usuario del prfil
                comm.CommandText = " SELECT   usu_id, usu_estado   FROM   tbl_usuario   WHERE   (usu_estado = @estado) ";
                comm.Parameters.Add("@estado", SqlDbType.VarChar).Value = 1;
                reader = comm.ExecuteReader();
                comm.Parameters.Clear();

                while (reader.Read())
                {

                    string userid = "";
                    if (!reader.IsDBNull(0)) userid = reader.GetString(0);//usuid

                    comm2.CommandText = " SELECT  usu_id, papp_id, permiso, restricted  FROM   usuario_permiso WHERE  (papp_id = @pageid) AND (usu_id=@usuid) ";
                    comm2.Parameters.Add("@usuid", SqlDbType.VarChar).Value = userid;
                    comm2.Parameters.Add("@pageid", SqlDbType.VarChar).Value = page;

                    reader2 = comm2.ExecuteReader();
                    comm2.Parameters.Clear();
                    int existe = 0;
                    long perm = 0, rest = 0;
                    while (reader2.Read())
                    {
                        if (!reader2.IsDBNull(2)) perm = reader2.GetInt64(2);
                        if (!reader2.IsDBNull(3)) rest = reader2.GetInt64(3);
                        existe = 1;
                    }
                    reader2.Close();
                    if (existe == 0)
                    {
                        comm2.CommandText = " insert into usuario_permiso (usu_id, papp_id,  permiso, restricted) values ( @usuid, @pageid, @permiso,@restricted  ) ";
                        comm2.Parameters.Add("@usuid", SqlDbType.VarChar).Value = userid;
                        comm2.Parameters.Add("@pageid", SqlDbType.VarChar).Value = page;
                        comm2.Parameters.Add("@permiso", SqlDbType.BigInt).Value = permiso;
                        comm2.Parameters.Add("@restricted", SqlDbType.BigInt).Value = restricted;
                        comm2.ExecuteNonQuery();
                        comm2.Parameters.Clear();
                    }
                    else
                    {
                        comm2.CommandText = " update usuario_permiso set  permiso=@permiso, restricted=@restricted  where  usu_id=@usuid and papp_id= @pageid ";
                        comm2.Parameters.Add("@usuid", SqlDbType.VarChar).Value = userid;
                        comm2.Parameters.Add("@pageid", SqlDbType.VarChar).Value = page;
                        comm2.Parameters.Add("@permiso", SqlDbType.BigInt).Value = (perm + permiso);
                        comm2.Parameters.Add("@restricted", SqlDbType.BigInt).Value = (rest + restricted);
                        comm2.ExecuteNonQuery();
                        comm2.Parameters.Clear();

                    }
                }
                reader.Close();
            }
            
           
            transaction2.Commit();
            result = 1;
        }
        catch (Exception e)
        {
           
            transaction2.Rollback();
            return 0;
        } finally{
            if (conn!= null ){  conn.Dispose();  }
            if (conn2!= null ){  conn.Dispose();  }
            if (reader!= null ){  reader.Dispose();  }
            if (reader2 != null) { reader2.Dispose(); }
            if (comm != null) { comm.Dispose(); }
            if (comm2 != null) { comm2.Dispose(); }
        
        }

        return result;
    }

    
    internal static int updateUserPerRes(int app, int perfilid, long[] matperfilyes, long[] matperfilno, long[] matresyes, long[] matresno, int posmatperfilyes, int posmatperfilno, int posmatresyes, int posmatresno, RE_GenericBean2 viejorol)
    {
         int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;


            comm.CommandType = CommandType.Text;
            //actualizo las restricciones para cada usuario del prfil
            comm.CommandText = "SELECT tbl_usuario.usu_id, tbl_usuario.usu_permiso, tbl_usuario.usu_restricted FROM  tbl_usuario_perfil INNER JOIN tbl_usuario ON tbl_usuario_perfil.upe_usu_id = tbl_usuario.usu_id INNER JOIN tbl_perfil ON tbl_usuario_perfil.upe_per_id = tbl_perfil.per_id INNER JOIN tbl_perfil_aplicacion ON tbl_perfil.per_id = tbl_perfil_aplicacion.ape_per_id WHERE  (tbl_perfil_aplicacion.ape_apl_id = @app) AND (tbl_usuario.usu_estado = 1) AND (tbl_perfil.per_id = @perfil)";
            comm.Parameters.Add("@app", SqlDbType.VarChar).Value = app;
            comm.Parameters.Add("@perfil", SqlDbType.VarChar).Value = perfilid;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                long permisouser = 0; long restricteduser = 0;
                string userid = "";
                if (!reader.IsDBNull(0)) userid = reader.GetString(0);//usuid
                if (!reader.IsDBNull(1)) permisouser = long.Parse(reader.GetInt64(1).ToString());//permiso
                if (!reader.IsDBNull(2)) restricteduser = long.Parse(reader.GetInt64(2).ToString());//restricted
              
                //para permisos acceso en  caso YEs
                for (int i = 0; i < posmatperfilyes; i++ ) {

                    long p = long.Parse(matperfilyes[i].ToString());
                    long test = permisouser & p;
                    //si no tiene el permiso incluido
                    if (test != p){
                        permisouser = permisouser + p;
                    }
                
                   

                }
                //para permisos acceso en  caso NO
                for (int i = 0; i < posmatperfilno; i++)
                {
                    long p = long.Parse(matperfilno[i].ToString());
                    long test = permisouser & p;
                    //si  tiene el permiso incluido
                    if (test == p)
                    {
                        //ahora hay que ver que tampoco este en el rol

                        long pperrol = long.Parse(viejorol.strC1);
                        long testrol = pperrol & p;
                        if (testrol == p)
                        {
                            permisouser = permisouser - p;
                        }
                    }

                    

                }

                //para restricted en  caso YEs
                for (int i = 0; i < posmatresyes; i++)
                {
                    long p = long.Parse(matresyes[i].ToString());
                    long test = restricteduser & p;
                    //si no tiene el permiso incluido
                    if (test != p)
                    {
                        restricteduser = restricteduser + p;
                    }

                }
                //para permisos acceso en  caso NO
                for (int i = 0; i < posmatresno; i++)
                {
                    long p = long.Parse(matresno[i].ToString());
                    long test = restricteduser & p;
                    //si  tiene el permiso incluido
                    if (test == p)
                    {
                        //ahora hay que ver que tampoco este en el rol
                       
                        long presrol = long.Parse(viejorol.strC2);
                        long testrol = presrol & p;
                        if (testrol == p){
                            restricteduser = restricteduser - p;
                        }
                    }

                }
                DB2.updatedata("tbl_usuario", "usu_permiso", "usu_id", userid, 0, "", Int64.Parse(permisouser.ToString()), 0);
                DB2.updatedata("tbl_usuario", "usu_restricted", "usu_id", userid, 0, "", Int64.Parse(restricteduser.ToString()), 0);

            }
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result; 
       
    
    }

    internal static string getPages(int app)
    {

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null, reader2 = null;
        RE_GenericBean2 reapp, reapp2;
        string xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?><pages>";
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;


            comm.CommandType = CommandType.Text;
            //actualizo las restricciones para cada usuario del prfil
            comm.CommandText = " SELECT    tbl_pageapp.papp_id, tbl_pageapp.papp_name, tbl_option.opc_permiso, tbl_pageapp.papp_path_image, tbl_option.opc_section, tbl_option.opc_id   FROM   tbl_pageapp INNER JOIN  tbl_option ON tbl_pageapp.papp_id = tbl_option.opc_pagina    WHERE    (tbl_option.opc_permiso = 1) AND (tbl_pageapp.papp_apl_id = @appid)   ORDER BY tbl_pageapp.papp_id ";
            comm.Parameters.Add("@appid", SqlDbType.Int).Value = app;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                reapp = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) reapp.intC1 = reader.GetInt32(0);//papp_id
                if (!reader.IsDBNull(1)) reapp.strC1 = reader.GetString(1);//papp_name
                if (!reader.IsDBNull(2)) reapp.int64C1 = reader.GetInt64(2);//opc_permiso
                if (!reader.IsDBNull(3)) reapp.strC2 = reader.GetString(3);//path_image
                if (!reader.IsDBNull(4)) reapp.intC2 = reader.GetInt32(4);//opc_section
                if (!reader.IsDBNull(5)) reapp.intC3 = reader.GetInt32(5);//tbl_option.opc_id

                xml += "<page><id>"+reapp.intC1+"</id><name>"+reapp.strC1+"</name><type>1</type><permit>"+reapp.int64C1+"</permit><image>"+reapp.strC2+"</image><idsection>"+reapp.intC2+"</idsection><idopc>"+reapp.intC3+"</idopc>";
                xml += "</page>";
            }
            comm.Parameters.Clear();
            comm.CommandText = "";
            reader.Close();

            

            //comm.CommandText = " SELECT   tbl_option.opc_pagina, tbl_option.opc_name, tbl_option.opc_permiso, tbl_option.opc_id    FROM    tbl_pageapp INNER JOIN   tbl_option ON tbl_pageapp.papp_id = tbl_option.opc_pagina    WHERE    (tbl_option.opc_permiso <> 1) AND (tbl_pageapp.papp_apl_id = @appid)   ORDER BY tbl_pageapp.papp_id ";
            comm.CommandText = " SELECT   tbl_option.opc_pagina, tbl_option.opc_name, tbl_option.opc_permiso, tbl_option.opc_id, tbl_section_page.section_id, tbl_section_page.section_name, tbl_option.orden   FROM   tbl_pageapp INNER JOIN   tbl_option ON tbl_pageapp.papp_id = tbl_option.opc_pagina INNER JOIN   tbl_section_page ON tbl_option.opc_section = tbl_section_page.section_id   WHERE   (tbl_option.opc_permiso <> 1) AND (tbl_pageapp.papp_apl_id = @appid)   ORDER BY tbl_pageapp.papp_id, tbl_section_page.section_id, tbl_option.orden ";
            comm.Parameters.Add("@appid", SqlDbType.Int).Value = app;
            reader2 = comm.ExecuteReader();

            while (reader2.Read())
            {
                reapp2 = new RE_GenericBean2();
                if (!reader2.IsDBNull(0)) reapp2.intC1 = reader2.GetInt32(0);//opc_gagina
                if (!reader2.IsDBNull(1)) reapp2.strC1 = reader2.GetString(1);//opc_name
                if (!reader2.IsDBNull(2)) reapp2.int64C1 = reader2.GetInt64(2);//opc_permiso
                if (!reader2.IsDBNull(3)) reapp2.intC2 = reader2.GetInt32(3);//opc_id
                if (!reader2.IsDBNull(4)) reapp2.intC3 = reader2.GetInt32(4);//section_id
                if (!reader2.IsDBNull(5)) reapp2.strC2 = reader2.GetString(5);//section_name
                if (!reader2.IsDBNull(6)) reapp2.intC4 = reader2.GetInt32(6);//orden

                xml += "<option><idpage>" + reapp2.intC1 + "</idpage><name>" + reapp2.strC1 + "</name><permit>" + reapp2.int64C1 + "</permit><idoption>"+reapp2.intC2+"</idoption><idsection>"+reapp2.intC3+"</idsection><namesection>"+reapp2.strC2+"</namesection><orden>"+reapp2.intC4+"</orden>";
                xml += "</option>";
            }
            reader2.Close();

            xml += "</pages>";
        }
        catch (Exception ex)
        {
            xml = "";
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return xml; 
       
    
    }

    internal static DataSet getPagesds(int app)
    {

        SqlConnection conn = null; SqlConnection conn2 = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        RE_GenericBean2 reapp;
        DataSet result;
        result = new DataSet();
        result.Tables.Add("tabla");
        result.Tables["tabla"].Columns.Add("name");
        result.Tables["tabla"].Columns.Add("permiso");
        result.Tables["tabla"].Columns.Add("papp_id");
        result.Tables["tabla"].Columns.Add("opc_id");
              
        try
        {
            conn = OpenConnection();
            conn2 = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;


            comm.CommandType = CommandType.Text;
            //actualizo las restricciones para cada usuario del prfil
            comm.CommandText = "SELECT    tbl_pageapp.papp_name, tbl_option.opc_permiso, tbl_pageapp.papp_id, tbl_option.opc_id    FROM   tbl_pageapp  INNER JOIN  tbl_option ON tbl_pageapp.papp_id = tbl_option.opc_pagina    WHERE   (tbl_option.opc_permiso = @app) AND (tbl_pageapp.papp_apl_id = 1)";
            comm.Parameters.Add("@app", SqlDbType.VarChar).Value = app;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                reapp = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) reapp.strC1 = reader.GetString(0);//name
                if (!reader.IsDBNull(1)) reapp.int64C1 = reader.GetInt64(1);//permiso
                if (!reader.IsDBNull(2)) reapp.intC1 = reader.GetInt32(2);//papp_id
                if (!reader.IsDBNull(3)) reapp.intC2 = reader.GetInt32(3);//papp_id
                object[] objArr = { reapp.strC1, reapp.decC1, reapp.intC1, reapp.intC2};
                result.Tables["Tabla"].Rows.Add(objArr);
            }

            reader.Close();
        }
        catch (Exception ex)
        {
            result = null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int deletePage(int app, int pageid) {
        int result=0;
        SqlConnection conn = null;
        SqlTransaction transaction = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();


        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            transaction = conn.BeginTransaction();


            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (pageid != null)
            {

               
                comm.CommandText = " delete from tbl_option where  opc_pagina = @parent ";
                comm.Parameters.Add("@parent", SqlDbType.Int).Value = pageid;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = " delete from  tbl_pageapp where papp_id = @opcionid and papp_apl_id=@appid ";
                comm.Parameters.Add("@appid", SqlDbType.VarChar).Value = app;
                comm.Parameters.Add("@opcionid", SqlDbType.VarChar).Value = pageid;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = " DELETE   FROM   tbl_perfil_aplicacion   WHERE   (ape_apl_id = @apl_id) AND (ape_papp_id = @papp_id)";
                comm.Parameters.Add("@apl_id", SqlDbType.Int).Value = app;
                comm.Parameters.Add("@papp_id", SqlDbType.Int).Value = pageid;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = " DELETE FROM usuario_permiso   WHERE   (papp_id = @papp_id)";
                comm.Parameters.Add("@papp_id", SqlDbType.Int).Value = pageid;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = " DELETE   FROM   tbl_section_page   WHERE   (sec_papp_id = @papp_id)";
                comm.Parameters.Add("@papp_id", SqlDbType.Int).Value = pageid;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = " DELETE   FROM   tbl_page_n1   WHERE   (pg1_apl_id = @apl_id) AND (pg1_papp_id = @papp_id)";
                comm.Parameters.Add("@apl_id", SqlDbType.Int).Value = app;
                comm.Parameters.Add("@papp_id", SqlDbType.Int).Value = pageid;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = " DELETE   FROM   tbl_page_n2   WHERE   (pg2_papp_id = @papp_id)";
                comm.Parameters.Add("@papp_id", SqlDbType.Int).Value = pageid;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = " DELETE   FROM   tbl_page_n3   WHERE   (pg3_papp_id = @papp_id)";
                comm.Parameters.Add("@papp_id", SqlDbType.Int).Value = pageid;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                
                result = 1;
                transaction.Commit();
            }
        }
        catch (Exception e)
        {
            transaction.Rollback();
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }


        return result;
    
    }

    internal static int deleteOption(int id, string parent)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlTransaction transaction = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();


        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            transaction = conn.BeginTransaction();


            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (id != null)
            {
               

                comm.CommandText = "delete from  tbl_option where opc_id=@opcionid and opc_pagina=@pagina ";
                comm.Parameters.Add("@pagina", SqlDbType.VarChar).Value = parent;
                comm.Parameters.Add("@opcionid", SqlDbType.VarChar).Value = id;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();



                result = 1;
                transaction.Commit();
            }
        }
        catch (Exception e)
        {
            transaction.Rollback();
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }


        return result;
    }

    internal static bool InsertarOption(RE_GenericBean2 opcion)
    {
        bool result = false;
        ArrayList lisPermit = new ArrayList();
        RE_GenericBean2 permits;

        SqlConnection conn = null;
        SqlTransaction transaction = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();


        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            transaction = conn.BeginTransaction();


            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (opcion != null)
            {
                long permisopt= 1;
                comm.CommandText = " SELECT max(opc_permiso) AS per FROM tbl_option where opc_pagina =@parent ";
                comm.Parameters.Add("@parent", SqlDbType.VarChar).Value = opcion.intC3;//parent
                reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) permisopt = reader.GetInt64(0);//maximo permiso para esa pagina
                }

                reader.Close();
                comm.Parameters.Clear();

                permisopt = permisopt * 2;

                int orden = 0;
                comm.CommandText = " SELECT max(orden) AS orden FROM tbl_option WHERE (opc_pagina = @parent) AND (opc_section = @section) ";
                comm.Parameters.Add("@parent", SqlDbType.Int).Value = opcion.intC3;//parent
                comm.Parameters.Add("@section", SqlDbType.Int).Value = opcion.intC2;//section
                reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) orden = reader.GetInt32(0);//orden
                }
                reader.Close();
                comm.Parameters.Clear();

                orden += 1;

                comm.CommandText = " insert into tbl_option (opc_name, opc_pagina, opc_permiso, opc_section, orden) values (@nombre, @parent, @permiso, @section, @orden) ";
                comm.Parameters.Add("@parent", SqlDbType.VarChar).Value = opcion.intC3;//parent
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = opcion.strC1;//nombre
                comm.Parameters.Add("@permiso", SqlDbType.BigInt).Value = permisopt;
                comm.Parameters.Add("@section", SqlDbType.Int).Value = opcion.intC2;//section
                comm.Parameters.Add("@orden", SqlDbType.Int).Value = orden;//orden
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = " SELECT   ape_per_id, ape_papp_id, ape_restricted   FROM   tbl_perfil_aplicacion   WHERE   (ape_papp_id = @parent) ";
                comm.Parameters.Add("@parent", SqlDbType.Int).Value = opcion.intC3;
                reader = comm.ExecuteReader();
                
                while (reader.Read())
                {
                    permits = new RE_GenericBean2();
                    if (!reader.IsDBNull(0)) permits.intC1 = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) permits.intC2 = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) permits.int64C1 = reader.GetInt64(2);
                    lisPermit.Add(permits);
                }
                reader.Close();
                comm.Parameters.Clear();

                foreach (RE_GenericBean2 per in lisPermit)
                {
                    comm.CommandText = " UPDATE   tbl_perfil_aplicacion   SET   ape_restricted = @restricted   WHERE   (ape_papp_id = @parent) AND (ape_per_id = @perfil) ";
                    comm.Parameters.Add("@parent", SqlDbType.Int).Value = opcion.intC3;
                    comm.Parameters.Add("@perfil", SqlDbType.Int).Value = per.intC1;
                    comm.Parameters.Add("@restricted", SqlDbType.BigInt).Value = (per.int64C1 + permisopt);
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }

               result = true;
               transaction.Commit();
               updateUsuario_Permiso(opcion.intC3, 0, permisopt);
            }
        }
        catch (Exception e)
        {
            transaction.Rollback();
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            result = false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int updateOpcionApp(int parent, int opcionid, string name, int section)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            int orden = 0;
            comm.CommandText = " SELECT max(orden) AS orden FROM tbl_option WHERE (opc_pagina = @parent) AND (opc_section = @section) ";
            comm.Parameters.Add("@parent", SqlDbType.Int).Value = parent;//parent
            comm.Parameters.Add("@section", SqlDbType.Int).Value = section;//section
            reader = comm.ExecuteReader();
            while (reader.Read())
            {
                if (!reader.IsDBNull(0)) orden = reader.GetInt32(0);//orden
            }
            reader.Close();
            comm.Parameters.Clear();

            orden += 1;

            comm.CommandType = CommandType.Text;
            if (section == 0)
            {
                comm.CommandText = " UPDATE tbl_option SET opc_name = @name   WHERE   opc_id = @opcionid AND opc_pagina = @parent ";
                comm.Parameters.Add("@parent", SqlDbType.Int).Value = parent;
                comm.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
                comm.Parameters.Add("@opcionid", SqlDbType.VarChar).Value = opcionid;
            }
            else
            {
                comm.CommandText = " UPDATE tbl_option SET opc_name = @name, opc_section = @section, orden = @orden   WHERE   opc_id = @opcionid AND opc_pagina = @parent ";
                comm.Parameters.Add("@parent", SqlDbType.Int).Value = parent;
                comm.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
                comm.Parameters.Add("@opcionid", SqlDbType.VarChar).Value = opcionid;
                comm.Parameters.Add("@section", SqlDbType.Int).Value = section;
                comm.Parameters.Add("@orden", SqlDbType.Int).Value = orden;
            }
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();
            result = 1;

        }

        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int updateOppMenu(int table, int idoption, string name, string url)
    {
        int result = 0;
        string tabla = "", nombre = "", ruta = "", criterio = "";
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            switch (table)
            {
                case 0: tabla = "tbl_menu"; nombre = "men_name"; ruta = "men_url"; criterio = "men_id"; break;
                case 1: tabla = "tbl_menu_opcion"; nombre = "mopc_name"; ruta = "mopc_url"; criterio = "mopc_id"; break;
                case 2: tabla = "tbl_menu_subopcion"; nombre = "mopc_name"; ruta = "mopc_url"; criterio = "mopc_id"; break;
            }

            comm.CommandType = CommandType.Text;
            comm.CommandText = " UPDATE   " + tabla + "  SET  " + nombre + " = @name, " + ruta + " = @url   WHERE   " + criterio + " = @criterio ";
            comm.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
            comm.Parameters.Add("@url", SqlDbType.VarChar).Value = url;
            comm.Parameters.Add("@criterio", SqlDbType.Int).Value = idoption;

            comm.ExecuteNonQuery();
            comm.Parameters.Clear();
            result = 1;
        }

        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int deleteOppMenu(int table, int idoption, string menu, string option)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlTransaction transaction = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            transaction = conn.BeginTransaction();

            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;

            //0 -> Tabla, 1 -> PK, 2 -> FK, 3 -> Si es opcion
            string[,] info = new string[3, 4] { { "tbl_menu", "men_id", "", "men_option" }, { "tbl_menu_opcion", "mopc_id", "mopc_men_id", "mopc_option" }, { "tbl_menu_subopcion", "mopc_id", "mopc_mopc_id", "" } };

            if (option.Equals("NO") || option == "")
            {
                if (table == 1 || table == 2)
                {
                    int llave = 0;
                    comm.CommandText = " SELECT   " + info[table, 2] + "   FROM   " + info[table, 0] + "   WHERE   (" + info[table, 1] + " = @criterio) ";
                    comm.Parameters.Add("@criterio", SqlDbType.Int).Value = idoption;
                    reader = comm.ExecuteReader();
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0)) llave = reader.GetInt32(0);//
                    }
                    comm.Parameters.Clear();
                    reader.Close();

                    int cont = 0;
                    comm.CommandText = " SELECT   COUNT(*) AS Expr1   FROM   " + info[table, 0] + "   WHERE   (" + info[table, 2] + " = @criterio) ";
                    comm.Parameters.Add("@criterio", SqlDbType.Int).Value = llave;
                    reader = comm.ExecuteReader();
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0)) cont = reader.GetInt32(0);
                    }
                    comm.Parameters.Clear();
                    reader.Close();

                    if ((cont - 1) == 0)
                    {
                        comm.CommandText = " UPDATE  " + info[(table - 1), 0] + "  SET  " + info[(table - 1), 3] + " = @valor  WHERE (" + info[(table - 1), 1] + " = @criterio) ";
                        comm.Parameters.Add("@valor", SqlDbType.VarChar).Value = "NO";
                        comm.Parameters.Add("@criterio", SqlDbType.Int).Value = llave;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                }

                comm.CommandText = " DELETE FROM  " + info[table, 0] + "  WHERE  " + info[table, 1] + " = @criterio ";
                comm.Parameters.Add("@criterio", SqlDbType.Int).Value = idoption;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            else
            {
                if (table == 1)
                {
                    int llave = 0;
                    comm.CommandText = " SELECT   " + info[table, 2] + "   FROM   " + info[table, 0] + "   WHERE   (" + info[table, 1] + " = @criterio) ";
                    comm.Parameters.Add("@criterio", SqlDbType.Int).Value = idoption;
                    reader = comm.ExecuteReader();
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0)) llave = reader.GetInt32(0);//
                    }
                    comm.Parameters.Clear();
                    reader.Close();

                    int cont = 0;
                    comm.CommandText = " SELECT   COUNT(*) AS Expr1   FROM   " + info[table, 0] + "   WHERE   (" + info[table, 2] + " = @criterio) ";
                    comm.Parameters.Add("@criterio", SqlDbType.Int).Value = llave;
                    reader = comm.ExecuteReader();
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0)) cont = reader.GetInt32(0);
                    }
                    comm.Parameters.Clear();
                    reader.Close();

                    if ((cont - 1) == 0)
                    {
                        comm.CommandText = " UPDATE  " + info[(table - 1), 0] + "  SET  " + info[(table - 1), 3] + " = @valor  WHERE (" + info[(table - 1), 1] + " = @criterio) ";
                        comm.Parameters.Add("@valor", SqlDbType.VarChar).Value = "NO";
                        comm.Parameters.Add("@criterio", SqlDbType.Int).Value = llave;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                }

                comm.CommandText = " DELETE FROM  " + info[table, 0] + "  WHERE  " + info[table, 1] + " = @criterio ";
                comm.Parameters.Add("@criterio", SqlDbType.Int).Value = idoption;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                if (table == 0)
                {
                    ArrayList opcList = null;
                    comm.CommandText = " SELECT     mopc_id   FROM   tbl_menu_opcion   WHERE   (mopc_men_id = @criterio) AND (mopc_option = 'YES') ";
                    comm.Parameters.Add("@criterio", SqlDbType.Int).Value = idoption;
                    reader = comm.ExecuteReader();
                    while (reader.Read())
                    {
                        opcList = new ArrayList();
                        if (!reader.IsDBNull(0)) opcList.Add(reader.GetInt32(0));
                    }
                    comm.Parameters.Clear();
                    reader.Close();

                    if(opcList!=null)
                        foreach (int opcion in opcList)
                        {
                            comm.CommandText = " DELETE FROM tbl_menu_subopcion   WHERE   (mopc_mopc_id = @criterio)";
                            comm.Parameters.Add("@criterio", SqlDbType.Int).Value = opcion;
                            comm.ExecuteNonQuery();
                            comm.Parameters.Clear();
                        }
                }
                

                comm.CommandText = " DELETE FROM  " + info[(table + 1), 0] + "  WHERE  " + info[(table + 1), 2] + " = @criterio ";
                comm.Parameters.Add("@criterio", SqlDbType.Int).Value = idoption;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            transaction.Commit();
            result = 1;
        }
        catch (Exception e)
        {
            transaction.Rollback();
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        
        return result;
    }

    internal static int addOppMenu(int table, int idoption, string menu, string option, string name, string url, int permiso)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlTransaction transaction = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            transaction = conn.BeginTransaction();

            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;

            //0 -> Tabla, 1 -> PK, 2 -> FK, 3 -> Si es opcion
            string[,] info = new string[3, 4] { { "tbl_menu", "men_id", "", "men_option" }, { "tbl_menu_opcion", "mopc_id", "mopc_men_id", "mopc_option" }, { "tbl_menu_subopcion", "mopc_id", "mopc_mopc_id", "" } };

            if (idoption == 0)
            {
                if (table == 0)
                {
                    int orden = 0;
                    comm.CommandText = " SELECT   MAX(orden) AS Expr1   FROM   tbl_menu   WHERE   (men_type = @menu) ";
                    comm.Parameters.Add("@menu", SqlDbType.Int).Value = menu;
                    reader = comm.ExecuteReader();
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0)) orden = reader.GetInt32(0);//orden
                    }
                    comm.Parameters.Clear();
                    reader.Close();

                    orden += 1;

                    comm.CommandText = " INSERT INTO tbl_menu  (men_type, men_papp_id, men_opc_id, men_name, men_url, men_target, orden, men_option)  VALUES  (@menu, 0, @opc_id, @name, @url, '_self', @orden, 'NO') ";
                    comm.Parameters.Add("@menu", SqlDbType.Int).Value = menu;
                    comm.Parameters.Add("@opc_id", SqlDbType.Int).Value = permiso;
                    comm.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
                    comm.Parameters.Add("@url", SqlDbType.VarChar).Value = url;
                    comm.Parameters.Add("@orden", SqlDbType.Int).Value = orden;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
            }
            else
            {
                if (table == 0 || table == 1)
                {
                    int orden = 0;
                    string paramopc1 = "", paramopc2 = "";
                    if (table == 0)
                    {
                        paramopc1 = ", " + info[(table + 1), 3];
                        paramopc2 = ", 'NO'";
                    }
                    comm.CommandText = " SELECT   MAX(orden) AS Expr1   FROM   " + info[(table + 1), 0] + "   WHERE   (" + info[(table + 1), 2] + " = @criterio) ";
                    comm.Parameters.Add("@criterio", SqlDbType.Int).Value = idoption;
                    reader = comm.ExecuteReader();
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0)) orden = reader.GetInt32(0);
                    }
                    comm.Parameters.Clear();
                    reader.Close();

                    orden += 1;

                    comm.CommandText = " INSERT INTO  " + info[(table + 1), 0] + "  (" + info[(table + 1), 2] + ", mopc_papp_id, mopc_opc_id, mopc_name, mopc_url, mopc_target, orden" + paramopc1 + ")  VALUES  (@criterio, 0, @opc_id, @name, @url, '_self', @orden" + paramopc2 + ") ";
                    comm.Parameters.Add("@criterio", SqlDbType.Int).Value = idoption;
                    comm.Parameters.Add("@opc_id", SqlDbType.Int).Value = permiso;
                    comm.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
                    comm.Parameters.Add("@url", SqlDbType.VarChar).Value = url;
                    comm.Parameters.Add("@orden", SqlDbType.Int).Value = orden;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();

                    if (option == "NO")
                    {
                        comm.CommandText = " UPDATE  " + info[table, 0] + "  SET  " + info[table, 3] + " = @opcion  WHERE  (" + info[table, 1] + " = @idopc ) ";
                        comm.Parameters.Add("@opcion", SqlDbType.VarChar).Value = "YES";
                        comm.Parameters.Add("@idopc", SqlDbType.Int).Value = idoption;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                }
            }
            transaction.Commit();
            result = 1;
        }
        catch (Exception e)
        {
            transaction.Rollback();
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        return result;
    }

    internal static string clientProductFee(string CLIENTID, string PRODUCTID)
    {
        string result="";
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT upr_precio  from [tbl_usuario_precio] where  upr_usu_id =@usuid and upr_prod_id=@prodid"; 
            comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = CLIENTID;
            comm.Parameters.Add("@prodid", SqlDbType.Int).Value = PRODUCTID;
            reader = comm.ExecuteReader();
            int p = 0;
            while (reader.Read())
            {
                if (!reader.IsDBNull(0))
                {
                    result = reader.GetFloat(0).ToString();
                }
            }
            reader.Close();
        }
        catch (Exception ex)
        {
            result = "";
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static string newUserAlias()
    {
        string result = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            comm.CommandType = CommandType.Text;
           // comm.CommandText = " SELECT   TOP (1) usu_alias   FROM   tbl_usuario   WHERE   (usu_alias LIKE '0%')   ORDER BY usu_alias DESC ";
            comm.CommandText = "SELECT TOP (1)  CASE WHEN isnumeric(usu_alias) = 1 THEN CAST(usu_alias AS integer) ELSE 0 END AS Expr1 FROM tbl_usuario ORDER BY Expr1 DESC " ;
            reader = comm.ExecuteReader();
            
            while (reader.Read())
            {
                if (!reader.IsDBNull(0)) result = reader.GetInt32(0).ToString();
            }
            reader.Close();
            if (result == null)
            {
                result = "001";
            }
            else
            {
                int valor= int.Parse(result) + 1;
               
                result=   valor.ToString("000");
               
               // result = "X" + (int.Parse(aux[1]) + 1);
            }
        }
        catch (Exception ex)
        {
            result = null;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static string getMenus(int app, int menu)
    {
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null, reader2 = null;
        RE_GenericBean2 reapp, reapp2;
        string xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?><menus>";
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            comm.CommandType = CommandType.Text;
            comm.CommandText = " SELECT   tbl_menu.men_id, tbl_menu.men_papp_id, CASE WHEN tbl_menu.men_name IS NULL THEN UPPER(tbl_pageapp.papp_name) ELSE tbl_menu.men_name END AS men_name, tbl_option.opc_permiso, tbl_menu.men_url, tbl_menu.men_target, tbl_menu.men_option, tbl_menu.orden   FROM   tbl_menu INNER JOIN   tbl_pageapp ON tbl_menu.men_papp_id = tbl_pageapp.papp_id INNER JOIN   tbl_option ON tbl_menu.men_opc_id = tbl_option.opc_id   WHERE   (tbl_menu.men_type = @menu)   ORDER BY tbl_menu.orden ";
            comm.Parameters.Add("@menu", SqlDbType.Int).Value = menu;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                reapp = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) reapp.intC1 = reader.GetInt32(0);//men_id
                if (!reader.IsDBNull(1)) reapp.intC2 = reader.GetInt32(1);//men_papp_id
                if (!reader.IsDBNull(2)) reapp.strC1 = reader.GetString(2);//men_name
                if (!reader.IsDBNull(3)) reapp.int64C1 = reader.GetInt64(3);//opc_permiso
                if (!reader.IsDBNull(4)) reapp.strC2 = reader.GetString(4);//men_url
                if (!reader.IsDBNull(5)) reapp.strC3 = reader.GetString(5);//men_target
                if (!reader.IsDBNull(6)) reapp.strC4 = reader.GetString(6);//men_option
                if (!reader.IsDBNull(7)) reapp.intC3 = reader.GetInt32(7);//orden

                xml += "<menu><men_id>" + reapp.intC1 + "</men_id><papp_id>" + reapp.intC2 + "</papp_id><men_name>" + reapp.strC1 + "</men_name><opc_permiso>" + reapp.int64C1 + "</opc_permiso><men_url>" + reapp.strC2 + "</men_url><men_target>" + reapp.strC3 + "</men_target><men_option>" + reapp.strC4 + "</men_option><men_orden>" + reapp.intC3 + "</men_orden>";
                xml += "</menu>";
            }
            comm.Parameters.Clear();
            comm.CommandText = "";
            reader.Close();

            comm.CommandText = " SELECT   tbl_menu_opcion.mopc_id, tbl_menu_opcion.mopc_papp_id, tbl_menu_opcion.mopc_men_id, CASE WHEN tbl_menu_opcion.mopc_name IS NULL THEN UPPER(tbl_pageapp.papp_name) ELSE tbl_menu_opcion.mopc_name END AS mopc_name, tbl_option.opc_permiso, tbl_menu_opcion.mopc_url, tbl_menu_opcion.mopc_target, tbl_menu_opcion.mopc_option, tbl_menu_opcion.orden   FROM   tbl_pageapp INNER JOIN  tbl_menu_opcion ON tbl_pageapp.papp_id = tbl_menu_opcion.mopc_papp_id INNER JOIN  tbl_option ON tbl_menu_opcion.mopc_opc_id = tbl_option.opc_id INNER JOIN  tbl_menu ON tbl_menu_opcion.mopc_men_id = tbl_menu.men_id   WHERE   (tbl_menu.men_type = @menu)   ORDER BY  tbl_menu_opcion.mopc_men_id, tbl_menu_opcion.orden ";
            comm.Parameters.Add("@menu", SqlDbType.Int).Value = menu;
            reader2 = comm.ExecuteReader();

            while (reader2.Read())
            {
                reapp2 = new RE_GenericBean2();
                if (!reader2.IsDBNull(0)) reapp2.intC1 = reader2.GetInt32(0);//mopc_id
                if (!reader2.IsDBNull(1)) reapp2.intC2 = reader2.GetInt32(1);//mopc_papp_id
                if (!reader2.IsDBNull(2)) reapp2.intC3 = reader2.GetInt32(2);//mopc_men_id
                if (!reader2.IsDBNull(3)) reapp2.strC1 = reader2.GetString(3);//mopc_name
                if (!reader2.IsDBNull(4)) reapp2.int64C1 = reader2.GetInt64(4);//opc_permiso
                if (!reader2.IsDBNull(5)) reapp2.strC2 = reader2.GetString(5);//mopc_url
                if (!reader2.IsDBNull(6)) reapp2.strC3 = reader2.GetString(6);//mopc_target
                if (!reader2.IsDBNull(7)) reapp2.strC4 = reader2.GetString(7);//mopc_option
                if (!reader2.IsDBNull(8)) reapp2.intC4 = reader2.GetInt32(8);//orden

                xml += "<submenu><mopc_id>" + reapp2.intC1 + "</mopc_id><papp_id>" + reapp2.intC2 + "</papp_id><men_id>" + reapp2.intC3 + "</men_id><mopc_name>" + reapp2.strC1 + "</mopc_name><opc_permiso>" + reapp2.int64C1 + "</opc_permiso><mopc_url>" + reapp2.strC2 + "</mopc_url><mopc_target>" + reapp2.strC3 + "</mopc_target><mopc_option>" + reapp2.strC4 + "</mopc_option><mopc_orden>" + reapp2.intC4 + "</mopc_orden>";  
                xml += "</submenu>";
            }
            comm.Parameters.Clear();
            comm.CommandText = "";
            reader2.Close();


            comm.CommandText = " SELECT   tbl_menu_subopcion.mopc_id, tbl_menu_subopcion.mopc_mopc_id, tbl_menu_subopcion.mopc_papp_id, CASE WHEN tbl_menu_subopcion.mopc_name IS NULL THEN UPPER(tbl_pageapp.papp_name) ELSE tbl_menu_subopcion.mopc_name END AS mopc_name, tbl_option.opc_permiso, tbl_menu_subopcion.mopc_url, tbl_menu_subopcion.mopc_target, tbl_menu_subopcion.orden   FROM   tbl_option INNER JOIN  tbl_menu_subopcion ON tbl_option.opc_id = tbl_menu_subopcion.mopc_opc_id INNER JOIN  tbl_menu INNER JOIN  tbl_menu_opcion ON tbl_menu.men_id = tbl_menu_opcion.mopc_men_id ON   tbl_menu_subopcion.mopc_mopc_id = tbl_menu_opcion.mopc_id INNER JOIN  tbl_pageapp ON tbl_menu_subopcion.mopc_papp_id = tbl_pageapp.papp_id   WHERE   (tbl_menu.men_type = @menu)   ORDER BY  tbl_menu_subopcion.mopc_mopc_id, tbl_menu_subopcion.orden ";
            comm.Parameters.Add("@menu", SqlDbType.Int).Value = menu;
            reader2 = comm.ExecuteReader();

            while (reader2.Read())
            {
                reapp2 = new RE_GenericBean2();
                if (!reader2.IsDBNull(0)) reapp2.intC1 = reader2.GetInt32(0);//mopc_id
                if (!reader2.IsDBNull(1)) reapp2.intC2 = reader2.GetInt32(1);//mopc_mopc_id
                if (!reader2.IsDBNull(2)) reapp2.intC3 = reader2.GetInt32(2);//mopc_papp_id
                if (!reader2.IsDBNull(3)) reapp2.strC1 = reader2.GetString(3);//mopc_name
                if (!reader2.IsDBNull(4)) reapp2.int64C1 = reader2.GetInt64(4);//opc_permiso
                if (!reader2.IsDBNull(5)) reapp2.strC2 = reader2.GetString(5);//mopc_url
                if (!reader2.IsDBNull(6)) reapp2.strC3 = reader2.GetString(6);//mopc_target
                if (!reader2.IsDBNull(7)) reapp2.intC4 = reader2.GetInt32(7);//orden

                xml += "<suboption><mopc_id>" + reapp2.intC1 + "</mopc_id><submenu_id>" + reapp2.intC2 + "</submenu_id><papp_id>" + reapp2.intC3 + "</papp_id><mopc_name>" + reapp2.strC1 + "</mopc_name><opc_permiso>" + reapp2.int64C1 + "</opc_permiso><mopc_url>" + reapp2.strC2 + "</mopc_url><mopc_target>" + reapp2.strC3 + "</mopc_target><sopcorden>" + reapp2.intC4 + "</sopcorden>";
                xml += "</suboption>";
            }
            comm.Parameters.Clear();
            comm.CommandText = "";
            reader2.Close();

            xml += "</menus>";
        }
        catch (Exception ex)
        {
            xml = "";
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return xml;
    }


    internal static string getPagesView(int app)
    {
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null, reader2 = null;
        RE_GenericBean2 reapp, reapp2;
        string xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?><pages>";
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            comm.CommandType = CommandType.Text;
            comm.CommandText = " SELECT   tbl_page_n1.pg1_id, tbl_page_n1.pg1_apl_id, tbl_page_n1.pg1_papp_id, tbl_page_n1.pg1_name, tbl_page_n1.pg1_path, tbl_page_n1.pg1_type   FROM   tbl_page_n1 INNER JOIN  tbl_pageapp ON tbl_page_n1.pg1_papp_id = tbl_pageapp.papp_id   WHERE   (tbl_page_n1.pg1_apl_id = @apl)   ORDER BY tbl_page_n1.orden ";
            comm.Parameters.Add("@apl", SqlDbType.Int).Value = app;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                reapp = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) reapp.intC1 = reader.GetInt32(0);//pg1_id
                if (!reader.IsDBNull(1)) reapp.intC2 = reader.GetInt32(1);//pg1_apl_id
                if (!reader.IsDBNull(2)) reapp.intC3 = reader.GetInt32(2);//pg1_papp_id
                if (!reader.IsDBNull(3)) reapp.strC1 = reader.GetString(3);//pg1_name
                if (!reader.IsDBNull(4)) reapp.strC2 = reader.GetString(4);//pg1_path
                if (!reader.IsDBNull(5)) reapp.intC4 = reader.GetInt32(5);//pg1_type

                xml += "<page><id>" + reapp.intC1 + "</id><apl_id>" + reapp.intC2 + "</apl_id><papp_id>" + reapp.intC3 + "</papp_id><name>" + reapp.strC1 + "</name><path>" + reapp.strC2 + "</path><type>" + reapp.intC4 + "</type>";
                xml += "</page>";
            }
            comm.Parameters.Clear();
            comm.CommandText = "";
            reader.Close();

            comm.CommandText = " SELECT   tbl_page_n2.pg2_id, tbl_page_n2.pg1_id, tbl_page_n2.pg2_papp_id, tbl_page_n2.pg2_name, tbl_page_n2.pg2_path, tbl_page_n2.pg2_type   FROM   tbl_page_n1 INNER JOIN tbl_page_n2 ON tbl_page_n1.pg1_id = tbl_page_n2.pg1_id INNER JOIN tbl_pageapp ON tbl_page_n1.pg1_papp_id = tbl_pageapp.papp_id   WHERE   (tbl_page_n1.pg1_apl_id = @apl)  ORDER BY tbl_page_n2.orden ";
            comm.Parameters.Add("@apl", SqlDbType.Int).Value = app;
            reader2 = comm.ExecuteReader();

            while (reader2.Read())
            {
                reapp2 = new RE_GenericBean2();
                if (!reader2.IsDBNull(0)) reapp2.intC1 = reader2.GetInt32(0);//pg2_id
                if (!reader2.IsDBNull(1)) reapp2.intC2 = reader2.GetInt32(1);//pg1_id
                if (!reader2.IsDBNull(2)) reapp2.intC3 = reader2.GetInt32(2);//papp_id
                if (!reader2.IsDBNull(3)) reapp2.strC1 = reader2.GetString(3);//pg2_name
                if (!reader2.IsDBNull(4)) reapp2.strC2 = reader2.GetString(4);//pg2_path
                if (!reader2.IsDBNull(5)) reapp2.intC4 = reader2.GetInt32(5);//pg2_type

                xml += "<subpage><id>" + reapp2.intC1 + "</id><pg1_id>" + reapp2.intC2 + "</pg1_id><papp_id>" + reapp2.intC3 + "</papp_id><name>" + reapp2.strC1 + "</name><path>" + reapp2.strC2 + "</path><type>" + reapp2.intC4 + "</type>";
                xml += "</subpage>";
            }
            comm.Parameters.Clear();
            comm.CommandText = "";
            reader2.Close();


            comm.CommandText = " SELECT   tbl_page_n3.pg3_id, tbl_page_n3.pg2_id, tbl_page_n3.pg3_papp_id, tbl_page_n3.pg3_name, tbl_page_n3.pg3_path, tbl_page_n3.pg3_type   FROM   tbl_page_n1 INNER JOIN tbl_page_n2 ON tbl_page_n1.pg1_id = tbl_page_n2.pg1_id INNER JOIN tbl_page_n3 ON tbl_page_n2.pg2_id = tbl_page_n3.pg2_id INNER JOIN tbl_pageapp ON tbl_page_n1.pg1_papp_id = tbl_pageapp.papp_id   WHERE   (tbl_page_n1.pg1_apl_id = @apl)   ORDER BY tbl_page_n3.orden ";
            comm.Parameters.Add("@apl", SqlDbType.Int).Value = app;
            reader2 = comm.ExecuteReader();

            while (reader2.Read())
            {
                reapp2 = new RE_GenericBean2();
                if (!reader2.IsDBNull(0)) reapp2.intC1 = reader2.GetInt32(0);//pg3_id
                if (!reader2.IsDBNull(1)) reapp2.intC2 = reader2.GetInt32(1);//pg2_id
                if (!reader2.IsDBNull(2)) reapp2.intC3 = reader2.GetInt32(2);//papp_id
                if (!reader2.IsDBNull(3)) reapp2.strC1 = reader2.GetString(3);//pg3_name
                if (!reader2.IsDBNull(4)) reapp2.strC2 = reader2.GetString(4);//pg3_path
                if (!reader2.IsDBNull(5)) reapp2.intC4 = reader2.GetInt32(5);//pg3_type

                xml += "<suboption><id>" + reapp2.intC1 + "</id><pg2_id>" + reapp2.intC2 + "</pg2_id><papp_id>" + reapp2.intC3 + "</papp_id><name>" + reapp2.strC1 + "</name><path>" + reapp2.strC2 + "</path><type>" + reapp2.intC4 + "</type>";
                xml += "</suboption>";
            }
            comm.Parameters.Clear();
            comm.CommandText = "";
            reader2.Close();

            xml += "</pages>";
        }
        catch (Exception ex)
        {
            xml = "";
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return xml;
    }


    internal static DataSet getDefaultProducts()
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("Product_id");//1
        result.Tables["Tabla"].Columns.Add("Name");//2
        result.Tables["Tabla"].Columns.Add("Initial");//3
        result.Tables["Tabla"].Columns.Add("orden");//4
        result.Tables["Tabla"].Columns.Add("fee");//5
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection2();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT  product_id, name, initial,orden, convert( varchar, client_fee) from product where estado=1 and default_client=1 order by orden asc";

            reader = comm.ExecuteReader();
            RE_GenericBean2 rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //productid
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//name
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//initial
                if (!reader.IsDBNull(3)) rgb.intC2 = reader.GetInt32(3);//orden
                if (!reader.IsDBNull(4)) rgb.strC3 = reader.GetString(4);//client_fee

                object[] objArr = { rgb.intC1, rgb.strC1, rgb.strC2, rgb.intC4, rgb.strC3 };
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();

        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(DateTime.Now+"Error al condsultar productos en WS"+ e.Message.ToString()+ e.StackTrace);
            escritor.Close();
            return null;
        }
        return result;

    }

}
